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

[Creative_tree88]

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 

[SamWiseOwl]

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]

@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 · Accepted solution]

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.