Forum Discussion

Mr_Glister's avatar
Mr_Glister
Icon for Advocate II rankAdvocate II
2 years ago

Calculate difference between moving average value of two different dates

Hi, 

I have daily observations with a bit of variability in their measurements, so I'm calculating a 30 day moving average. 

I do so with the following DAX and it works fine:

 

My daily weight moving average = 

 

 

Smartavgmoving =
VAR _Maxdate = MAX( 'Date table'[Date])
RETURN  CALCULATE( AVERAGE( Table[My daily weight]), DATESBETWEEN( 'Date table'[Date], _Maxdate - 15, _Maxdate +15))

 

 

As expected the output looks like this:

 

I want to calculate the rate of change between certain periods (for example, between every first and last of a month) but no matter how I try to pass a date filter to my moving average calculation, the outcome is always the table above. 

For example, the measure below results in the same table as posted above. 

 

=CALCULATE( Smartavgmoving, Table[DATE] = dt"2023-01-17"))

 

 

Can somebody point me in the right direction?

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Mr_Glister , Based on what I got, Try measures like

     

    First of month =
    VAR _Maxdate = Eomonth(MAX( 'Date table'[Date]), -1) +1
    RETURN CALCULATE( AVERAGE( Table[My daily weight]), DATESBETWEEN( 'Date table'[Date], _Maxdate - 15, _Maxdate +15))


    Last of Month=
    VAR _Maxdate = Eomonth(MAX( 'Date table'[Date]), 0)
    RETURN CALCULATE( AVERAGE( Table[My daily weight]), DATESBETWEEN( 'Date table'[Date], _Maxdate - 15, _Maxdate +15))

    • Mr_Glister's avatar
      Mr_Glister
      Icon for Advocate II rankAdvocate II
      2 years ago

      Hi amitchandak,

      (Nice to get help from one of the forum heroes!)

      I will try your suggestion and I'm sure it will work for this narrow case. But the real reason I posted this to the forum is the following question:

       

      Since my original measure correctly calculates a value for each date, shouldn't I be able to directly access the values of each date? For example, if - for some reason - I wanted to calculate the difference of exactly the two dates I highlighted. Or put differently, why does the DAX below not work and how would I have to do it correctly?

      =CALCULATE( Smartavgmoving, Table[DATE] = dt"2023-01-17"))