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.
I have the following query below calculating the latest date per ID to give me an outcome, what i want to try and do is get the date previous to the max so the 2nd to last date is there an easy way i can amend the top VAR _maxdate to show 2nd last date rather than the max?
VAR _maxdate = CALCULATE ( MAX ( 'Table1'[Date] ), FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) ) ) RETURN CALCULATE ( MAX ( 'Table1'[outcome] ), FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) && 'Table1'[Date] = _maxdate ) )
Solved! Go to Solution.
Hi @Anonymous ,
According to your measure, it seems that you want to show the outcome of the 2nd last Date for each ID ,right?
I did it in two ways, please check.
1. Get the last date firstly and then use MAXX() to get the date which is < the last date, it will be the 2nd last date:
Measure1 =
VAR _last =
CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
VAR _2nd =
MAXX (
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] < _last ),
[Date]
)
RETURN
CALCULATE (
MAX ( 'Table1'[outcome] ),
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] = _2nd )
)
2.Use RANK() to sort by [Date] in descending order, then the 2nd last Date is [Rank]=2:
Rank =
RANKX (
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) ),
CALCULATE ( MAX ( ( 'Table1'[Date] ) ) ),
,
DESC
)
Measure2 =
CALCULATE (
MAX ( 'Table1'[outcome] ),
FILTER ( ALL ( Table1 ), [ID] = MAX ( 'Table1'[ID] ) && [Rank] = 2 )
)
Here is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data and show the expected result.
Hi @Anonymous ,
According to your measure, it seems that you want to show the outcome of the 2nd last Date for each ID ,right?
I did it in two ways, please check.
1. Get the last date firstly and then use MAXX() to get the date which is < the last date, it will be the 2nd last date:
Measure1 =
VAR _last =
CALCULATE ( MAX ( 'Table1'[Date] ), ALLEXCEPT ( Table1, Table1[ID] ) )
VAR _2nd =
MAXX (
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] < _last ),
[Date]
)
RETURN
CALCULATE (
MAX ( 'Table1'[outcome] ),
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) && [Date] = _2nd )
)
2.Use RANK() to sort by [Date] in descending order, then the 2nd last Date is [Rank]=2:
Rank =
RANKX (
FILTER ( ALL ( 'Table1' ), [ID] = MAX ( 'Table1'[ID] ) ),
CALCULATE ( MAX ( ( 'Table1'[Date] ) ) ),
,
DESC
)
Measure2 =
CALCULATE (
MAX ( 'Table1'[outcome] ),
FILTER ( ALL ( Table1 ), [ID] = MAX ( 'Table1'[ID] ) && [Rank] = 2 )
)
Here is the final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous When I have tackled something similar, I have used RANKEQ to rank the dates by ID and then found the max -1 that way.
@Anonymous Maybe:
VAR _maxdate1 =
CALCULATE (
MAX ( 'Table1'[Date] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) )
)
VAR __maxdate =
CALCULATE (
MAX ( 'Table1'[Date] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[ID] = MAX ( 'Table1'[ID]) && 'Table1'[Date]=__maxdate1 )
)
RETURN
CALCULATE (
MAX ( 'Table1'[outcome] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[ID] = MAX ( 'Table1'[ID])
&& 'Table1'[Date] = _maxdate
)
)
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |