How to calculate the max value and its predecessor

[o59393]

Hi all

How can I calculate the maximum value of a demand in a given month and its predecessor value versus the capacity of the max value of the demand and the predecessor capacity?

For example:

In this table my maximum demand is the value in yellow for december 2020. 

How can the measure calculate:

The max demand (yellow color) + the predecessor (green color) - the capacity value of the max demand row (blue color) + the predecessor capacity (grey color)

I attach the excel and pbix.

https://1drv.ms/u/s!ApgeWwGTKtFdhx92MNyIjJaTqapT?e=NQ78Nu

Thanks!

[dax · Accepted solution]

Hi @o59393 , 

You need to add month in Table  and use below measures to achieve this goal. You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[o59393]

@dax  wow

Thanks a lot, very powerful dax.

[o59393]

Hi  @dax 

I was replicating your solution into my final pbix, but I have an issue 

In your first part of the formula 

pre cap = VAR month = LOOKUPVALUE ( Capacidad[Month], Capacidad[Capacity], [max cap] )

and

pre dem = VAR month = LOOKUPVALUE ( Capacidad[Month], Capacidad[Demand], [max dem] )

The values Capacidad[Month] and Capacidad[Capacity] and Capacidad[Demand] in my final pbix are calculated columns and measures.

I think that is giving me a problem, because it's returning me the total capacity (32M), but should be the 2 months only:

Can you please advise me how to get it right in my pbix?

https://1drv.ms/u/s!ApgeWwGTKtFdhyEaO7IZgUhf2Q-l?e=pdlEoghttps://1drv.ms/u/s!ApgeWwGTKtFdhyEaO7IZgUh...

Thanks 

[amitchandak]

@o59393 , see if time intelligence can work

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

Check

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.