Forum Discussion

GarryPope's avatar
GarryPope
Advocate I
4 years ago
Solved

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

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. 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

Replies have been turned off for this discussion
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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