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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Query on month on month % calculation, highlight the cost variance

Hi Experts,

I have a cost of application wise splitup of month on month. Need your assistance to arrive the out come of the below mentioned points.

 

1) Need to compare the cost for previous month and highlight the cost if it is increased (for ex: Jan month - "ECC-IN" application cost has been increased for the month of Feb'20,  Mar'20 and May'20.  So, the same has been highlight in some color)

2) Need to present month on month cost variance % (comparing previous month)

 

Can any one please help me to procced further?

 

BI Commt..png

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

Hi  @Anonymous ,

 

Is your issue solved?If not,could you pls provide some raw data in editable format and expected output?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Hi Kelly - I have writtent the same query but the values are coming as "Zero". Have taken the cost value from Measure, not that might be the reason for "zero".

 

My requirements are

1) need to highlight a color if the cost is increased for the month ( have to compare with previous month)

2) Need to bring Month on Month % variation cost 

 

PBIX file is available in the google drive (refer Distributed cost - measure for calculation)

https://drive.google.com/file/d/1PQebk376zuuob_ExVg_9fWNgf19X59en/view?usp=sharing

 

Thanks,

Hi  @Anonymous ,

 

Is it a must to use matrix to implement the visual?Can a table visual be a replacement?

 

In my opinion,you'd better create another 2 measures to calculate the value for the current month and the previous month,then check whether there's an increasement and calculate the percentage.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Hi Kelly, Yes i corrected the calendar and now it is showing the MoM %....

 

Can you please help me how to enable the color coding for the cost...?

 

For ex:

Jan - 100, Feb - 250, Mar - 210, Apr - 300, May - 290 & Jun - 410 

I wan to highligh Feb, Apr & Jun month cost which is lower than the previous month.

 

thanks!

Anonymous
Not applicable

Sorry, It is higher than the preivous month...Thanks,

amitchandak
Super User
Super User

@Anonymous , you use time intelligence and date table

 

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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
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))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))
Next to next  month value =  CALCULATE(sum('table'[total hours value]),nextmonth(dateadd('Date'[Date],1,MONTH)))
previous to previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))
Last year same month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

 

Power BI — MTD
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors