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 mo...
Need Help
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"))