Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Running total

I need help with calculating running total for cales column on three basis, daily, weekly and monthly. and to always the starting point move forward as for daily 365 days from today, weekly 52 weeks ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

Anonymous First, I would go with this format: (3) Better Running Total - Microsoft Fabric Community

So your first measure would be: 

 

Better Daily RT =
    VAR __Date = MAX('Date'[Date])
    VAR __MinDate = __Date - 365
    VAR __Table = 
      SUMMARIZE(
        FILTER(ALLSELECTED('Table'),[Date] <= __Date && [Date] >= __MinDate),
        [Date],
        "__TotalSales", [Total Sales]
      )
    VAR __Result = SUMX( __Table, [__TotalSales]
RETURN
  __Result

 

For Monthly this:

 

Better Monthly RT =
    VAR __Date = MAX('Date'[Date])
    VAR __PrevDate = EOMONTH( __Date, -12)
    VAR __MinDate = DATE(YEAR(__PrevDate), MONTH(__PrevDate), 1)
    VAR __Table = 
      SUMMARIZE(
        FILTER(ALLSELECTED('Table'),[Date] <= __Date && [Date] >= __MinDate),
        [Date],
        "__TotalSales", [Total Sales]
      )
    VAR __Result = SUMX( __Table, [__TotalSales]
RETURN
  __Result

 

For weekly, I recommmed using Sequential and then the format is almost exactly the same.

Sequential - Microsoft Fabric Community