LASTNONBLANK -1, -2, -3 filter

[harry6810]

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!

[tamerj1]

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.]
)
)

[harry6810]

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? 

[tamerj1 · Accepted 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 )