Comparing the same month from last year

[davidr]

Hello,

I need to calculate the percentage difference of the 'Ratio' column (Column B) for the same month from last year.

For example: Comparing the 'Ratio' from 202007 (10) to 201907 (11) would be a 9.5% Difference.

Any help would be appreciated. Many Thanks.

YYYY	Ratio	Date
202007	10	1/07/2020
202006	12	1/06/2020
201907	11	1/07/2019
201906	15	1/06/2019

[Icey]

Hi @davidr ,

Please let us know if the methods above are helpful.

And if the problem is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[pranit828 · Accepted solution]

Hi @davidr 

Create a new column as below

_LOOK =

var _val = LOOKUPVALUE('Table'[Ratio], 'Table'[YYYY], FORMAT(left('Table'[YYYY],4),"####")-1 & right('Table'[YYYY],2))

//RETURN _val

RETURN if(ISBLANK(_val),BLANK(),(_val-'Table'[Ratio])/_val)

Below is the Output, I don't see 9.5%, you may change the calculation in the above column creation dax formula marked in blue to achieve your desired result.

[amitchandak]

@davidr , you can use time intelligence and date table

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
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))))
diff = [MTD Sales]-[last year MTD Sales]
diff % = divide([MTD Sales]-[last year MTD Sales],[last year 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-Y...


Appreciate your Kudos.

[mohammedadnant]

@davidr 

You can try this DAX measure... 

SPLY =

VAR _SPLYValue = CALCULATE(sum(LastYear[Ratio]), SAMEPERIODLASTYEAR(LastYear[Date]))

VAR _ThisYearValue = SUM(LastYear[Ratio])

VAR _Diff = _SPLYValue-_ThisYearValue

RETURN

IF(_Diff>0,

DIVIDE(_Diff,_SPLYValue,0)*100, "")

Thanks and hit the like button,

Mohammed Adnan

www.youtube.com/taik18

[Greg_Deckler]

@davidr - There are time "intelligence" functions for this like CALCULATE([somthing],PREVIOUSYEAR())

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...