Forum Discussion

AHS2019's avatar
AHS2019
Frequent Visitor
7 years ago
Solved

Help Please trying to replicate a variance analysis in Power BI

I am trying to replicate a time intelligence based variance analysis of the data which is over a 12 month period.

I would like my report to show the view for the current month vs the prior month however the report displays all the months in the data as screenshot below:

 

I would also like for the month on month vairance to show as one column for the current month and the previous month, however the quick measure i have created shows a variance column for each month even when a slicer has been added it will display a column for each month selected

 

My ideal visual would be as below 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    That will happen.  If you select 2 months in the slicer/filter, then for each month there will be a variance column.  As regards, the second question, i suggest you try the following measures:

    Total = SUM('SAP DATA'[Amt **bleep**.lc.cur])

    Total in previous month = CALCULATE([Total],PREVIOUSMONTH('Datekey'[Date]))

    Growth over previous month = IFERROR([Total]/[Total in previous month]-1,BLANK())

    Hope this helps.

7 Replies

    • AHS2019's avatar
      AHS2019
      Frequent Visitor
      7 years ago

      Ashish_Mathur  No this doesnt seem to work as this still displays a column for January variance against the previous month for which there is no data as this relates to the previous financial year.

       

       

      I also have a error message in my time intelligence based measure for my Month on month variance measure, could this be an issue?

       

       MoM% =
      IF(
          ISFILTERED('Datekey'[Date]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __PREV_MONTH =
              CALCULATE(
                  SUM('SAP DATA'[Amt **bleep**.lc.cur]),
                  DATEADD('Datekey'[Date].[Date], -1, MONTH)
              )
          RETURN
              DIVIDE(SUM('SAP DATA'[Amt **bleep**.lc.cur]) - __PREV_MONTH, __PREV_MONTH)

       

      Apologies if I seem like I dont kno what I am talking about I am quiet new to this and very much in learning phase at the moment

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        That will happen.  If you select 2 months in the slicer/filter, then for each month there will be a variance column.  As regards, the second question, i suggest you try the following measures:

        Total = SUM('SAP DATA'[Amt **bleep**.lc.cur])

        Total in previous month = CALCULATE([Total],PREVIOUSMONTH('Datekey'[Date]))

        Growth over previous month = IFERROR([Total]/[Total in previous month]-1,BLANK())

        Hope this helps.