Issue with running cumulative totals when there is no data for a time period

[BCMRC]

Hi Power BI Community - I am in deperate need for some help.

I am tracking sales on a daily basis and have created a matrix table that consists of actual daily sales & cumulative sales based on the date.

To do this I have created a date table and created a relationship as below:

The measure to create the cumulative total is:

CumulativeCount = 
IF (
    MIN ( LeadTable[ID] )
        <= CALCULATE ( MAX ( TICKETS[LeadTime] ), ALL ( TICKETS )),
                CALCULATE (
                    [TicketSales],
                    FILTER (
                        ALL(TICKETS),
                        [LeadTime] <= MIN (LeadTable[LeadTime])
                    )
                )
)

where [TicketSales] is a measure using COUNTROWS('TICKETS')

which works perfectly and enables me to produce the following table:

The issue I have is that the data only shows up until the last ticket sale. For example, in the table above, let's say todays date is Tuesday 19th June. You can see that data is only showing up until Saturday 16th of June because there were no ticket sales on Sunday 17th and Monday the 18th. 

I would like to show data up until todays date regardless if there has been ticket sales or not. In the case above, it would show either blank or 0's and the cumulative figure would also then kick in up until todays date.

Any help from you experts out there would be much appreciated!!!!

Thanks in advance

Cheers

[v-yulgu-msft]

Hi @BCMRC,

Please try this:

CumulativeCount = 
IF (
    MIN ( LeadTable[ID] )
        <= CALCULATE ( MAX ( TICKETS[LeadTime] ), ALL ( TICKETS )),
                CALCULATE (
                    [TicketSales],
                    FILTER (
                        ALL(TICKETS),
                        [LeadTime] <= MIN (LeadTable[LeadTime])
                    )
                )
)
+0

Regards,

Yuliana Gu

[BCMRC]

Hi @v-yulgu-msft

I already tried that but it caused more issues with the crossjoins as it is a matrix table

see below screenshot on what happens when putting a "+0" at the end of the measure

[BCMRC]

last call for any help from anybody out there? TIA

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[BCMRC]

Hi @Ashish_Mathur! 

thank you for your response

Please see the link below to download the pbix file. I've had to obviously create dummy data as i am working with confidential information

Download Here

Hope you can help

cheers

[BCMRC]

anyone?