Forum Discussion

davegw's avatar
davegw
Frequent Visitor
6 years ago
Solved

Cumulative/Running Total visual when some rows have no data

I need to create a runningtotal for sales based on the number of days before an event. My fact table includes a join to a key called DaysBeforeEventKey. The reason for using this rather than date is ...
Show and Tell
  • davegw's avatar
    davegw
    6 years ago

    I've now found a solution (I'm not sure it is the perfect soultion and may now contains some redundancy but at least it works).

     

    Here's my new version of the measure:-

    PCR Ticket Volume Running Total 2019 =
VAR LastDay =
    MAXX (
        FILTER (
            ( 'Fact Sales' ),
            RELATED ( 'Dimension Event'[Event Name] ) = "xxxx 2019"
        ),
        'Fact Sales'[DaysBeforeEventKey]
    )
VAR FirstDay =
    MINX (
        FILTER (
            ( 'Fact Sales' ),
            RELATED ( 'Dimension Event'[Event Name] ) = "xxxx 2019"
        ),
        'Fact Sales'[DaysBeforeEventKey]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) > LastDay,
        BLANK (),
        IF (
            SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) < FirstDay,
            BLANK (),
            CALCULATE (
                SUM ( 'Fact Sales'[Ticket Quantity] ),
                'Dimension Event'[Event Name] = "xxxx 2019",
                'Dimension Event'[EventYear] = 2019,
                FILTER (
                    ALLSELECTED ( 'Fact Sales' ),
                    AND (
                        'Fact Sales'[DaysBeforeEventKey]
                            <= SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ),
                        NOT ( ISBLANK ( SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) ) )
                    )
                )
            )
        )
    )

     

    And how my visual now looks

     

    Final Visual (As required)

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi davegw ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

 

davegw's avatar
davegw
Frequent Visitor
6 years ago

I've now found a solution (I'm not sure it is the perfect soultion and may now contains some redundancy but at least it works).

 

Here's my new version of the measure:-

PCR Ticket Volume Running Total 2019 =
VAR LastDay =
    MAXX (
        FILTER (
            ( 'Fact Sales' ),
            RELATED ( 'Dimension Event'[Event Name] ) = "xxxx 2019"
        ),
        'Fact Sales'[DaysBeforeEventKey]
    )
VAR FirstDay =
    MINX (
        FILTER (
            ( 'Fact Sales' ),
            RELATED ( 'Dimension Event'[Event Name] ) = "xxxx 2019"
        ),
        'Fact Sales'[DaysBeforeEventKey]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) > LastDay,
        BLANK (),
        IF (
            SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) < FirstDay,
            BLANK (),
            CALCULATE (
                SUM ( 'Fact Sales'[Ticket Quantity] ),
                'Dimension Event'[Event Name] = "xxxx 2019",
                'Dimension Event'[EventYear] = 2019,
                FILTER (
                    ALLSELECTED ( 'Fact Sales' ),
                    AND (
                        'Fact Sales'[DaysBeforeEventKey]
                            <= SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ),
                        NOT ( ISBLANK ( SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) ) )
                    )
                )
            )
        )
    )

 

And how my visual now looks

 

Final Visual (As required)