Forum Discussion

topazz11's avatar
topazz11
Helper III
7 years ago
Solved

Combined running total

Hello,

 

Trying to rite a measure to display running total by date and combine the two running totals.

I would like to have 3 measures as below.

 1. running total up to yesterday

 2. running total from today (including today)

 3. combine running total 1 &2 

Thanks!

**updated data

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi topazz11 ,

    I created a sample with some measures that you can have a try.

    • Create a calendar table

     

    Table 2 = CALENDARAUTO()

     

    • Create some measures

     

    Running total 1 (up to yesterday) =
IF (
    MAX ( 'Table'[Date ] ) >= DATE ( 2019, 7, 5 ) || MAX ( 'Table'[Date ] ) = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Item] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ] <= MAX ( 'Table 2'[Date] ) && 'Table'[Date ] < DATE ( 2019, 7, 5 )
        )
    )
)

    Running total 2 (from today) =
IF (
    MAX ( 'Table'[Date ] ) < DATE ( 2019, 7, 5 ) || MAX ( 'Table'[Date ] ) = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Item] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ] >= DATE ( 2019, 7, 5 ) && 'Table'[Date ] <= MAX ( 'Table 2'[Date] )
        )
    )
)

    Measure =
    IF (
    MAX ( 'Table'[Date ] ) < DATE ( 2019, 7, 5 ),
    [Running total 1 (up to yesterday)],
    [Running total 2 (from today)]
    )
    
Combine Runing =
    IF (
    'Table 2'[Measure] = BLANK (),
    BLANK (),
    CALCULATE (
    SUMX ( 'Table', [Measure] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date ] <= MAX ( 'Table 2'[Date] ) )
    )
    )

    Here is my sample that you can download.

     

2 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    7 years ago

    Hi topazz11 ,

    I created a sample with some measures that you can have a try.

    • Create a calendar table

     

    Table 2 = CALENDARAUTO()

     

    • Create some measures

     

    Running total 1 (up to yesterday) =
IF (
    MAX ( 'Table'[Date ] ) >= DATE ( 2019, 7, 5 ) || MAX ( 'Table'[Date ] ) = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Item] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ] <= MAX ( 'Table 2'[Date] ) && 'Table'[Date ] < DATE ( 2019, 7, 5 )
        )
    )
)

    Running total 2 (from today) =
IF (
    MAX ( 'Table'[Date ] ) < DATE ( 2019, 7, 5 ) || MAX ( 'Table'[Date ] ) = BLANK (),
    BLANK (),
    CALCULATE (
        SUM ( 'Table'[Item] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date ] >= DATE ( 2019, 7, 5 ) && 'Table'[Date ] <= MAX ( 'Table 2'[Date] )
        )
    )
)

    Measure =
    IF (
    MAX ( 'Table'[Date ] ) < DATE ( 2019, 7, 5 ),
    [Running total 1 (up to yesterday)],
    [Running total 2 (from today)]
    )
    
Combine Runing =
    IF (
    'Table 2'[Measure] = BLANK (),
    BLANK (),
    CALCULATE (
    SUMX ( 'Table', [Measure] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Date ] <= MAX ( 'Table 2'[Date] ) )
    )
    )

    Here is my sample that you can download.