Forum Discussion

Starstorm's avatar
Starstorm
Frequent Visitor
4 years ago
Solved

Period-to-Period Variance(s) & Percentage(s) Without Hardcoding

Hi all, I am new here - great to be in this wonderful community.   I need help with showing the variances (in values & percentages) between periods in a matrix table for at least 24 months automati...
Need Help
Show and Tell
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Starstorm , you can use the date table and time intelligence

 

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]))

 

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

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

  • Starstorm's avatar
    Starstorm
    Frequent Visitor
    4 years ago

    Thanks for the prompt response.

     

    I assume the above only works for the last two months i.e. 31-12-2022 and 30-11-2022, which is variance for current and prior months. Would it be possible to have the BI to automatically compute the variance for the rest of the previous months-on-months i.e. 30-11-2022 and 31-10-2022?

     

    My data is with time intelligence.

     

    Thanks again.