Forum Discussion

Creative_tree88's avatar
Creative_tree88
Icon for Helper V rankHelper 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 cou...
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.

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper 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
    Icon for Helper V rankHelper 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
      Icon for Super User rankSuper 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.