Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harry6810
Frequent Visitor

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!

1 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 )

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors