Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago
Solved

Difference per month between two different metrics in same data & Moving Average

Hi everyone.  I've attached some sample data - hoping this is an easy one for someone to solve but it's causing me some issues at the moment.

 

So, I have a dataset (sample linked below).  I am counting using the formula

 

=calculate(distinctcount('Sample Data 160525'[Event Key]))

 

The data is plotted onto a graph and I then use the column 'Type' in the Legend of the chart to view both activity and demand on the same chart.

What I need to do is now calculate the difference between activity and demand per month.  So, if activity is 10 in May and Demand is 15 in May, the new data needs to show a calculation of the difference of 5.  And this needs ot be replicated throughout the data (I have 7 years worth of data in the actual data so I need to be able to calculate this easily).

I also need to be able to show a moving average for the data (activity and demand) and this is going to be used on a separate chart.  Can anyone help with this request too?  If possible, calculate a 2 month moving average using the linked sample data.

Many thanks all.

 

Sample Data 

Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago
    Previous 2 month activity average =
    var initialdate = Dateadd(FIRSTDATE('calendar Table'[Date]), -2,MONTH)
    //return first day 2 months ago
    var finaldate = Dateadd(FIRSTDATE('calendar Table'[Date]), -1,DAY)
    //return last day of last month
    var Calc =
        CALCULATE(
            [activity] --perform measure
            ,DATESBETWEEN(
                'calendar Table'[Date]
                ,initialdate --filter to >=
                ,finaldate --filter <=
            )
        ) / 2 --divide by 2

    var final =
        if(
            finaldate <> BLANK() --If final date in range
            ,Calc --perform calculation
            ,BLANK()
        )
    RETURN final
     

    You will need to create a calendar table :
    https://www.wiseowl.co.uk/power-bi/blogs/power-bi-desktop/power-bi-dates/calendarauto-table/

    Join this to your Data Table using the date.

    Use the calendar table fields in the visual and the calculations.

3 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Creative_tree88 

    You could create a measure for each of the 3 calculations and have them in the Y axis and the secondary Y axis.

    activity = CALCULATE(DISTINCTCOUNT('Data Table'[Event Key]), 'Data Table'[Type] = "Activity")
    demand  = CALCULATE(DISTINCTCOUNT('Data Table'[Event Key]), 'Data Table'[Type] = "Demand")
    difference = activity - demand
     

     

    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      1 year ago

      SamWiseOwl  Many thanks.  I just wondered if there was an all encomassing way of using a DAX measure to calculate the difference between activity and demand without using activity - demand.  I might use it across other fields and it would be far less time consuming if I could just use one measure to calculate the difference every time.

      Also - did you also have any ideas around calculating a 2 month moving average on the data?  Thanks.

      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago
        Previous 2 month activity average =
        var initialdate = Dateadd(FIRSTDATE('calendar Table'[Date]), -2,MONTH)
        //return first day 2 months ago
        var finaldate = Dateadd(FIRSTDATE('calendar Table'[Date]), -1,DAY)
        //return last day of last month
        var Calc =
            CALCULATE(
                [activity] --perform measure
                ,DATESBETWEEN(
                    'calendar Table'[Date]
                    ,initialdate --filter to >=
                    ,finaldate --filter <=
                )
            ) / 2 --divide by 2

        var final =
            if(
                finaldate <> BLANK() --If final date in range
                ,Calc --perform calculation
                ,BLANK()
            )
        RETURN final
         

        You will need to create a calendar table :
        https://www.wiseowl.co.uk/power-bi/blogs/power-bi-desktop/power-bi-dates/calendarauto-table/

        Join this to your Data Table using the date.

        Use the calendar table fields in the visual and the calculations.