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

[Anonymous]

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?

[v-kelly-msft]

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]

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,

[v-kelly-msft]

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]

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]

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

[amitchandak]

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