Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to 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.
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.
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?
Thanks
@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.
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
182 | |
85 | |
67 | |
61 | |
53 |