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 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.

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.