Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |