cancel
Showing results for
Did you mean: Frequent Visitor

## LASTNONBLANK -1, -2, -3 filter

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!

1 ACCEPTED SOLUTION  Super User

@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 =
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 3  Super User

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

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?  Super User

@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 =
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 )`````` Announcements #### 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. #### 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! #### 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
Users online (2,434)