Forum Discussion

David1780's avatar
David1780
Frequent Visitor
3 years ago
Solved

Running Total (Quick Measure) not filtering when using a Date Table

Hello,

I have a Revenue Data from 2021-2023 in one Table which is linked to a Calendar Table as per the relationship below. When I create a Running Total measure (using the quick measure) which references the Revenue from 1 table and the Date from the Calendar Table, it works as expected but when I Filter the visual to a particular year (like just 2022), the running total doesnt start from zero as its keeps the previous years revenue in the calculation. 

Revenue running total in Date =
CALCULATE(
    SUM('QryMonthlyProjectRevenueReport'[Revenue]),
    FILTER(
        ALLSELECTED('Calendar'[Date]),
        ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
    )
)
All Revenue Data and Running Total:
 
Revenue Date filtered to 2022 and Running Total:

I would like the first value in the RT to be $332k then $766k.

 

Thanks in advance.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi, 

    I am not sure if the calendar table is assigned as a CALENDAR table, but please try something like below whether it produces the desired outcome or not.

     

    Revenue running total in Date =
CALCULATE (
    SUM ( 'QryMonthlyProjectRevenueReport'[Revenue] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi, 

    I am not sure if the calendar table is assigned as a CALENDAR table, but please try something like below whether it produces the desired outcome or not.

     

    Revenue running total in Date =
CALCULATE (
    SUM ( 'QryMonthlyProjectRevenueReport'[Revenue] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)