Forum Discussion

harry6810's avatar
harry6810
Frequent Visitor
3 years ago
Solved

LASTNONBLANK -1, -2, -3 filter

Hi there, 

how to calculate measures with LASTNONBLANK -1, -2, -3 etc (max string) filter?

 

Mytable

WeekSales
W01222
W02266
W03319
W04382
W05458
W06549
W07658
W08789
W09946
W101135
W111362
W121634
W131960

 

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's avatar
    tamerj1
    3 years ago

    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 )

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      harry6810
      Frequent Visitor
      3 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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 )