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 from current week and monthly 12 months from current month. I have the following measures that start from the first available date always, how can i add above conditions?

Daily RT =
VAR MaxDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],          
        'Date'[Date] <= MaxDate,  
        ALL ( 'Date' )              
    )
 
 
Weekly RT =
VAR MaxDate = MAX ( 'Date'[Week Start] )
RETURN
    CALCULATE (
        [Total Sales],            
        ALL ( 'Date'),
        'Date'[Week Start] <= MaxDate
    )
Monthly RT =
VAR MaxDate = MAX ( 'Date'[Month Start] )
RETURN
    CALCULATE (
        [Total Sales],            
        ALL ( 'Date'),
        'Date'[Month Start] <= MaxDate
    )
Need Help

2 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    thatnk you so much for your answer. It was very helpful. Now using these to functions  though I can calculate the runnin totals monthly and weekly but when plot them in a line chart, the month turns into days when choosing days for X axis, How can i plot them in a line chart and lines remain monthly and daily each:  

    RolleUp Month =
    VAR Period =
    DATESINPERIOD(
    'Date'[Date],
    MAX('Date'[Date]),
    -12,
    MONTH
    )
    VAR totalSales =
    CALCULATE(
    [Total Sales],
    Period
    )
    RETURN
    totalSales

     

    RolleUp Day =
    VAR Period =
    DATESINPERIOD(
    'Date'[Date],
    MAX('Date'[Date]),
    -365,
    DAY
    )
    VAR TotalSales =
    CALCULATE(
    [Total Sales],
    Period
    )
    RETURN
    TotalSales