Compare increase/decrease of previous two months using Date/Time column

[GarryPope]

Hello all,

I'm pretty new to Power BI, so please forgive me. I hope you can help please.

I have a table in Dataverse called App.

It has two columns:

appid

appcreatedon

Note: the appcreatedon is of the data type Date/Time

I want to create a visual where I show the percentage increase or decrease comparing last month to the month before. 

Example.
We are now in October.
I want to compare a count of the appid for last month (September) and compare them with the count of appid for last 2 months (August). So if September had 10 appid rows and August had 5 appid rows then it would be a percentage increase of 100%. And of course if I was to look at this in May next year it would show the comparision of April vs March. 

And please remember my date column appcreatedon is of the data type Date/Time.

Hope that makese sense? Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

Hi @GarryPope ,

Check the measure.

measure = 
var last_month = CALCULATE(COUNT('Table'[appid]),FILTER(ALL('Table'),'Table'[appcreatedon].[MonthNo]=SELECTEDVALUE('Table'[appcreatedon].[MonthNo])-1))
var last_2month = CALCULATE(COUNT('Table'[appid]),FILTER(ALL('Table'),'Table'[appcreatedon].[MonthNo]=SELECTEDVALUE('Table'[appcreatedon].[MonthNo])-2))
return
(last_month-last_2month)/last_2month

Best Regards,

Jay

[amitchandak]

@GarryPope , with help from time intelligence you can get last months

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

2nd previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA