Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
Here is my data sample,
I want to create a table that looks like this
I was able to get the month and the Last Read, but was not able to get the 2nd last
Here is the DAX that I have used:
2)
@Anonymous - Maybe:
Latest =
VAR __LatestDate = MAXX('Table',[Date])
RETURN
MAXX(FILTER('Table',[Date]=__LatestDate),[Read])
2nd Latest =
VAR __LatestDate = MAXX('Table',[Date])
VAR __2ndLatest = MAXX(FILTER('Table',[Date]<>__LatestDate),[Date])
RETURN
MAXX(FILTER('Table',[Date]=__2ndLatest),[Read])
Basically variations of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
@Anonymous , one way is to create rank column on date inside moth and use that
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
or try these measures
last value = CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]))
second last value =
var _max = CALCULATE(max(Table[Date]),DATESMTD('Date'[Date]))
return
CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]), filter(Date, Date[Date]<_max))
or
second last value =
var _max = CALCULATE(max(Table[Date]),DATESMTD('Date'[Date]))
return
CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]), filter(Table, Table[Date]<_max))
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
31 | |
19 | |
18 |