Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Running Total Continuing Past Date Filter

Good afternoon,

 

I am trying to add a running total to my graph, but using the built-in quick measure, the running total goes past the date filter (for the past 14 days). See image below,

 

 

The formula for the measure, 

 

Merge1.Volume running total in Day =
CALCULATE(
    SUM('Merge2'[Merge1.Volume]),
    FILTER(
        ALLSELECTED('Merge2'[Date].[Day]),
        ISONORAFTER('Merge2'[Date].[Day], MAX('Merge2'[Date].[Day]), DESC)
    )
)
Need Help
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Anonymous Try this: Better Running Total - Microsoft Power BI Community

    Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

    In your case you may want:

    Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date && [Date] <= TODAY())
RETURN
    SUMX(__Table,[Value])

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Try this: Better Running Total - Microsoft Power BI Community

    Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

    In your case you may want:

    Better RT = 
    VAR __Date = MAX('Table'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date && [Date] <= TODAY())
RETURN
    SUMX(__Table,[Value])