Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I need a DAX measure to return the string of the previous month. Example:
ID | Date | Level |
101 | 10/20 | Staff |
101 | 11/20 | Manager |
So if the date selected in the slicer is 11/20, I need the measure to return "Staff"
@Anonymous , do you have dates or month only. can share a better sample
@Anonymous , Create a date table and try measure like
previous month value = CALCULATE(max('Table'[Level]),previousmonth('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@Anonymous Try this:
File is attached below my signature.
Measure =
VAR MaxDate =
CALCULATE ( MAX ( bela[Date] ), ALLSELECTED ( bela ) )
VAR Temp =
FILTER ( ALL ( bela ), bela[Date] < MaxDate )
VAR LastDateInTable =
MAXX ( Temp, bela[Date] )
VAR LevelAtLastDate =
CALCULATE (
CONCATENATEX ( DISTINCT ( bela[Level] ), bela[Level], ", " ) ,
bela[Date] = LastDateInTable,
REMOVEFILTERS ( bela )
)
RETURN
LevelAtLastDate
@AntrikshSharma Hello
If I tell you all the columns are in different tables (that are connected), what changes in the measure?
@Anonymous If you can attach a sample file then that would help in testing the code.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |