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)

davegw's avatar
davegw
Frequent Visitor
6 years ago

HI v-lid-msft thanks for your proposal. I've tried it but it's still not giving me the answer I require. Here is how your solution looks when applied to my data model (same result as my Origanl Solution):-

Interestingly if I create a measure to calculate the value that should be returned to LastDay I get the expected answer (see test measure below) which correctly returns -102 which is the maximum DaysBeforeEventKey where the eventYear is 2020:-

The Days Slider Slicer, slices between a range of DaysbeforeEventKey.

 

The result I'm trying to achive is as below (Without the spikes in the red area and without the need to apply the days slicer manually). No tickets were sold  more than 181 days before the event (that's where they went on sales), No tikets have been sold less than 102 days before the event (becuase today is 102 days before the event). And there are a few days where no tickets were sold:-

Desired Result

 

My Data model is quite straightforward. It's a star shema with a composite PK on the Sales_Fact table and a FK relationship (with properly enforced constraints) to each Dimension Table.

Data Model

NB - Your suggestion is giving the same visual as my original one:-

Note Original and revised measures give same results

My Data Model is using Direct Query to SQL Server so it's not so easy to upload a copy of the data

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

Hi davegw ,

 

Sorry for late reply, We changed the formula to make it does not show data after last day, but we cannot reproduce the spikes on our sample data.

 

Running Total 2020 = 
VAR LastDay =
    CALCULATE (
        MAX ( 'Fact Sales'[DaysBeforeEventKey] ),
        ALLSELECTED('Fact Sales'),
        FILTER (
            ALLSELECTED ( 'Dimension Event' ),
            'Dimension Event'[EventYear] = 2020
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Fact Sales'[DaysBeforeEventKey] ) > LastDay,
        BLANK (),
        CALCULATE (
            SUM ( 'Fact Sales'[Ticket Quantity] ),
            'Dimension Event'[EventYear] = 2020,
            FILTER (
                ALLSELECTED ( 'Fact Sales' ),
                'Fact Sales'[DaysBeforeEventKey]
                    <= SELECTEDVALUE ( 'Dimension Days Before Event'[Days])
            )
        )
    )

 

 


Best regards,