Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
o59393
Post Prodigy
Post Prodigy

How to calculate the max value and its predecessor

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:

 

ssa.JPG

 

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!

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
dax
Community Support
Community Support

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.

@dax  wow

 

Thanks a lot, very powerful dax.

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

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.