Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rolling Calculation with Filter

I have a rolling sum calculation as my measure. The measure works fine, but the additonal requirement is to hide the last 7 days of calculated data.  The data older than 7 days should show up even if...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try measure like these, see if this can help

    measure =
    var _max = maxx(all(Date), Date[Date])
    return
    calculate(if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

     

    measure =
    var _max = maxx(all(Date), Date[Date])
    return
    Sumx(values(Date[Date]), if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks. I added a scenario in my post.  The goal is to calculate the moving sum for all dates except the last 7 available dates, as the date range advances each day.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Try measure like these, see if this can help

measure =
var _max = maxx(all(Date), Date[Date])
return
calculate(if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank

 

measure =
var _max = maxx(all(Date), Date[Date])
return
Sumx(values(Date[Date]), if(max(Date[Date]) >=_max-7, blank(), [measure])) // you can use 0 in place blank