Help needed on max week calculation

[Anonymous]

Hi All,

I am new at Power BI and especially I have to learn/explore dax.

I am working on my first dashboard, I have this simple table (our DWH works with snapshots):

Week_code	Number
202045	125.000
202044	124.000
202043	123.000
202042	122.000

My first question is to calculate the max week_code, this should be simple, but I am not able to get it right. I have this formula: Max week = CALCULATE(MAX(Datum[WEEK_CODE])), but this only give the max week per row. I also want this formula to respond to my week_code slicer. In this case the result should be 202045.

My second question is to calculate the Number according to this max week, my coding at this moment is this: In_base max week =
CALCULATE (
sum('Base week'[IN_BASE]), Datum[WEEK_CODE]="202045"
)

But this is not dynamic, I guess when I have the solution of max week, I can replace "202045" with max week and it should do the trick?

Can anyone help me out on this?
And can anyone give me suggestions where to find good learnings about dax?

Thanks in advance.

Sander

[Anonymous · Accepted solution]

Hi AlB,

thank for the answer, this almost does the trick perfectly, but....
The week_code in the date table is filled u/i 202253, so the max week in this solution is 202253 an the second measure ofcourse is empty.

What do I need to add to find the max week with data, my apologies, I forgot to metion that...

[AlB]

Hi @Anonymous 

I assume these are measures? It will depend on how you want to use them (what fields are present in the visual)  but try this:

Max week =
CALCULATE ( MAX ( Datum[WEEK_CODE] ), ALL ( Datum ) )

In_base max week =
VAR maxWeek_ = [Max week]
RETURN
    CALCULATE ( SUM ( 'Base week'[IN_BASE] ), Datum[WEEK_CODE] = maxWeek_ )

 As for resources, there's a lot of very good stuff out there. I'd recommend @MattAllington 's book and site to start with. He explains the main topics really well. And then of course SQLBI and their bible on DAX. The Italians know pretty much everything there's to know about DAX.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous · Accepted solution]

Hi AlB,

thank for the answer, this almost does the trick perfectly, but....
The week_code in the date table is filled u/i 202253, so the max week in this solution is 202253 an the second measure ofcourse is empty.

What do I need to add to find the max week with data, my apologies, I forgot to metion that...

[wdx223_Daniel · Accepted solution]

@Anonymous 

maxweek=calculate(max(datum[week_code]),allselected(datum))

maxnumber=var _maxwk=[maxweek] return calculate(sum(datum[number]),datum[week_code]=_maxwk,allselected(datum))

[AlB]

@Anonymous 

I don't understand the question

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers