Forum Discussion

rocky84's avatar
rocky84
Regular Visitor
4 years ago
Solved

Simple Variance Analysis

Hello all,   Hoping for some help as I am new to Power BI. I am being encouraged to use this at work for some simple financial analysis, but am struggling to do some standard variances, which i use...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    rocky84 , You have create measure for this month and last month and use those

     

     

    examples measures

     

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

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

    Variance

    diff = [MTD Sales]-[last MTD Sales]
    diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

     

    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

PBIdashboards's avatar
PBIdashboards
Icon for Post Patron rankPost Patron
3 months ago

The DAX approach in the accepted answer works well. Worth adding for anyone coming from Excel who finds this workflow: the measure-per-metric pattern scales up quickly once you add more months or more products, you end up maintaining a lot of measures.

For this exact use case (July vs Aug, Variance, % Variance across products), Flexa Tables on AppSource lets end-users add the variance column directly in the published report select the two periods, the absolute and % variance appear automatically. No DAX measures to write or maintain.

Free trial on AppSource