cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Help needed on max week calculation

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_codeNumber

202045

125.000
202044124.000
202043123.000
202042122.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

2 ACCEPTED SOLUTIONS

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...

 

View solution in original post

@Sander1977 

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

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

View solution in original post

4 REPLIES 4
Super User III
Super User III

Hi @Sander1977 

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 

SU18_powerbi_badge

 

 

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...

 

View solution in original post

@Sander1977 

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

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

View solution in original post

@Sander1977 

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 

 

SU18_powerbi_badge

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.