cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors