Forum Discussion

kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

'Rolling' Cumulative Measure value: previous 365 days

I am looking for help with DAX:

 

I am creating a line graph with the date along the X axis.

 

I'm looking to create a measure where for each data point along the x axis, it is calculated by looking at the previous 365 days.

 

Here's what I have, but it just accumulates starting at the beginning of time rather than limiting the scope to the previous 365 days:

 

Cumulative_Annual_Shrinkage =
CALCULATE(
    [Adjusted_%_vALUE],
    FILTER(
        ALLSELECTED('Date_Filter'[Date]),
        ISONORAFTER('Date_Filter'[Date], MAX('Date_Filter'[Date]), DESC)
    )
)
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    kpost You should be able to do this:

    Measure = 
  VAR __Date = MAX('Date_Filter'[Date])
  VAR __MinDate= __Date - 365
  VAR __Result = CALCULATE([Adjusted_%_Value], 'Date_Filter'[Date] >= __MinDate && 'Date_Filter'[Date] <= __Date)
RETURN
  __Result

     

    If that doesn't work you can try an alternate approach here:

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    kpost You should be able to do this:

    Measure = 
  VAR __Date = MAX('Date_Filter'[Date])
  VAR __MinDate= __Date - 365
  VAR __Result = CALCULATE([Adjusted_%_Value], 'Date_Filter'[Date] >= __MinDate && 'Date_Filter'[Date] <= __Date)
RETURN
  __Result

     

    If that doesn't work you can try an alternate approach here: