Cumulative/Running Total visual when some rows have no data

[davegw]

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 becuase the event occurs on a different date each year and ultimately I need to overlay sales for this year with last year based on the number of days left until the event. The screenshot below shows what I have but I have two problems:-

1.  As of today it's 102 days before the event. I need my line to stop at -102 days and not 'flat line' Based on the measure below it works if I replace 'LastDay' with literal of -102 but the VAR doesn't seem to calculate to '-102'. What am I doing wrong?

2. There are some days where there are no sales (including all of the days before selling started). These give invalid 'spikes' in my visual. How can I get rid of these? I'm struggling to determine exactly how to solve this problem.

Thanks in advance for any help.

[v-lid-msft]

Hi @davegw ,

We can try to create a measure to meet your requirement:

Running Total 2020 =
VAR LastDay =
    CALCULATE (
        MAX ( 'Fact Sales'[DaysBeforeEventKey] ),
        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 ( 'Fact Sales'[DaysBeforeEventKey] )
            )
        )
    )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[davegw]

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]

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 · Accepted solution]

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]

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,