Cummulative total displaying blank row for dates having no data

[anandav]

Hi,

I have 2 x tables:

1. Date (Date, Month)

    Date table has dates from 01/01/2008 to 08/03/2008.

2. Sales (Sales date, Quantity)

    Sales table have sales quantity data from 01/01/2008 to 15/01/2008 and then from 16/02/2008 to 25/02/2008.

I use the below Measure to calculate the cumulative total:

Cumulative Date Quantity =
IF (
    MIN ( 'Date'[Calendar Date] )
        <= CALCULATE ( MAX ( CalendarDateBased[Sales Date] ), ALL ( CalendarDateBased ) ),
    CALCULATE (
       SUM ( CalendarDateBased[Quantity] ),
       FILTER (
         ALL ( 'Date'[Calendar Date] ),
         'Date'[Calendar Date] <= MAX('Date'[Calendar Date])
      )
  )

)

Problem: Though the sales ends 25/02/2008, since there is March 2008 dates in Date table, when the data is drilled down  by Month in a matrix, it displays an additional blank row for March. (See image).

How can I avoid this additional line?

But if I create a seperate Matrix with CalendarMonth and Cummulative Total then it works correctly - meaning no blank row for March.

Any help would be greatly appreciated.

[v-huizhn-msft · Accepted solution]

Hi @anandav,

In your first matrix, you add the MaxDate and MinDate, so there a additional row because there are values for MaxDate and MinDate. For your second matrix, it doesn't display the blank because the row with no data is disabled by default. If you click it, it still shows the blank row(please review the following screenshot).



For your issue, if you want to remove the additional line.  We can not do it directly becasue the whole row is nonblank(MaxDate and MinDate). Please try the following solution and check if it works fine.

Create a measure using the formula, then add it as visual level filter, filter the visual when the [Blank or not] is 1.

Blank or not =
IF ( ISBLANK ( CalendarDateBased[Cumulative Date Quantity] ), 0, 1 )

Best Regards,
Angelia

[anandav]

Hi @v-huizhn-msft,

Thanks for your explanation Angelia.

Once I removed the Max and Min columns it worked. I had those columns for learning purposes to understand the cummulative calculation.

Thanks again.