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

datahaytahs247's avatar
datahaytahs247
New Member
30 days ago

When calculating variance, the difference is provided as a positive number. I have a data set where I am attempting to calculate Y1 planned - Y1 actual, and it should generate negative numbers in some cases. Can you explain why Power BI does not allow the difference to be negative numbers?