Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

rolling sum for last 3 months

Hi,

I am calculating the rolling sum of last 3 months within tabular model.I wrote a dax like :

3_MonthRollingIncident:=calculate(sum(Damage[Incident]),DATESINPERIOD('Date'[Date],lastdate('Date'[Date]),-3,month))

This gives me the desired output but when i use Incident column from fact table into my pivot,it creates extra values/rows that are not in the source.

How do i specify the context or group by condition on the above formula to say "Just group by SiteName".What is happening right now i think is when I drag Year-month,sitename and 3 monthRolling ,it gives me correct values,but when i drag Incident column,it groups by every granular incident value.

Issue comes when i pull in Incident and other columns:

 

 

 

 

Show and Tell

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I did not find any solution yet,however,I found that measure Rolling sum is grouped by distinct of Incident values over last 3 months and that is generating extra rows.

      What i am looking for right now is,how do i modify the measure to explicitly indicate "group data by just SiteName and Date field and not by other columns" and avoid all the mess.

      Measure: 3_MonthRollingIncident:=calculate(sum(Quality[Incident]),DATESINPERIOD('Date'[Date],lastdate('Date'[Date]),-3,MONTH))

      Pls provide a solution,.