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 there,
how to calculate measures with LASTNONBLANK -1, -2, -3 etc (max string) filter?
Mytable
Week | Sales |
W01 | 222 |
W02 | 266 |
W03 | 319 |
W04 | 382 |
W05 | 458 |
W06 | 549 |
W07 | 658 |
W08 | 789 |
W09 | 946 |
W10 | 1135 |
W11 | 1362 |
W12 | 1634 |
W13 | 1960 |
What I am trying to do is to get sum of sales for Week 11.
I've tried like this but no effect.
=CALCULATE(sum(Mytable[Sales]),LASTNONBLANK(Mytable[Week],3))
Thanks for any help!
Solved! Go to Solution.
@harry6810
Actually MAX can handle both numbers and texts. But in the case of text the behaviour is different; for example "W2" is greater than "W19" However, in your case the format is "W02" and "W19" and this should still give the correct result using MAX or MAXX. Therefore, the solution should work normally with the [Week] column.
=
CALCULATE (
SUM ( Mytable[Sales] ),
Mytable[Week No.]
= MINX (
TOPN ( 3, VALUES ( Mytable[Week No.] ), Mytable[Week No.] ),
Mytable[Week No.]
)
)
If for any reason an integer column is still required, then it can be calculated on the fly using either ADDCOLUMNS or SELECTCOLUMNS. Something as follows should also work
=
VAR T1 =
ADDCOLUMNS (
VALUES ( Mytable[Week] ),
"@WeekNum", VALUE ( RIGHT ( Mytable[Week], 2 ) )
)
VAR T2 =
TOPN ( 3, T1, [@WeekNum] )
VAR T3 =
TOPN ( 1, T2, [@WeekNum], ASC )
VAR ThirdMaxWeek =
MAXX ( T3, [Week] )
RETURN
CALCULATE ( SUM ( Mytable[Sales] ), Mytable[Week No.] = ThirdMaxWeek )
Hi @harry6810
you have to have a week number column (integer data type). Then you may try
=
CALCULATE (
SUM ( Mytable[Sales] ),
Mytable[Week No.]
= MINX (
TOPN ( 3, VALUES ( Mytable[Week No.] ), Mytable[Week No.] ),
Mytable[Week No.]
)
)
Hi @tamerj1 , thank you for the tip. And what if I can not have 'week' as an integer data type? Is there something in DAX ie MaxString() function in QS?
@harry6810
Actually MAX can handle both numbers and texts. But in the case of text the behaviour is different; for example "W2" is greater than "W19" However, in your case the format is "W02" and "W19" and this should still give the correct result using MAX or MAXX. Therefore, the solution should work normally with the [Week] column.
=
CALCULATE (
SUM ( Mytable[Sales] ),
Mytable[Week No.]
= MINX (
TOPN ( 3, VALUES ( Mytable[Week No.] ), Mytable[Week No.] ),
Mytable[Week No.]
)
)
If for any reason an integer column is still required, then it can be calculated on the fly using either ADDCOLUMNS or SELECTCOLUMNS. Something as follows should also work
=
VAR T1 =
ADDCOLUMNS (
VALUES ( Mytable[Week] ),
"@WeekNum", VALUE ( RIGHT ( Mytable[Week], 2 ) )
)
VAR T2 =
TOPN ( 3, T1, [@WeekNum] )
VAR T3 =
TOPN ( 1, T2, [@WeekNum], ASC )
VAR ThirdMaxWeek =
MAXX ( T3, [Week] )
RETURN
CALCULATE ( SUM ( Mytable[Sales] ), Mytable[Week No.] = ThirdMaxWeek )
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
32 | |
30 | |
18 | |
18 |