Cumulative Total with Table Filter

[patrickcox]

Hello,
I am trying to understand why one measure works and another does not. I have a simple model with a Sales Table (Adventureworks) and a Dates table. The Adventureworks Sales table does not have sales on every date but rather only on a few selected dates. And I want to calculate cumulative sales and only display dates with sales. I found a measure on this board that works and I have pasted it below, however I thought another measure would also work and it does not. So my question is, why is the 2nd measure not working? Thanks!

This measure from Sam works…

Cumulative Sales allselected dates table remove blanks =
var CumulativeSales = CALCULATE([Sales],
FILTER(ALLSELECTED(‘Dates’),
‘Dates’[Date] <= MAX(‘Dates’[Date])
)
)
RETURN
IF(ISBLANK([Sales]),BLANK(),CumulativeSales)

in this Measure, I used a table filter on the Sales table but when I do this, the cumulative part of the measure does not work. I guess the ‘Sales Orders’ table filter is overriding the Dates filter above? Is there another way to apply this filter? Is the If(ISBLANK() solution above the only way to tackle this?

Cumulative Sales allselected dates table with Sales Table Filter =
CALCULATE([Sales],
FILTER(ALLSELECTED(‘Dates’),
‘Dates’[Date] <= MAX(‘Dates’[Date])
),
‘Sales Orders’
)

Here is the result of the two measures…

Thanks!!

[AntrikshSharma]

@patrickcox

Working With Expanded Tables In Power BI

[patrickcox]

@AntrikshSharma , Thanks for sharing your video. It was very interesting. However I am not certain what to take from it in the context of my issue. I believe you are suggesting that I should not invoke the expanded table in my measure by referencing the ‘Sales Orders’ table as a filter. However if I don’t do that and if I don’t use the If(Isblank()) evaulation then all dates from the ‘Dates’ table are listed in the visual. (See attached.)

So, is the “If(Isblank()” evaluation the best solution to remove the dates with no values in the ‘Sales Orders’ table or is there a better solution? I keep thinking that there is a way to filter rather than use the If statement. Thanks again for your help!

[Heather]

Remember that when dealing with measures, sometimes you need to look at each piece in isolation to best understand it

VAR CummulativeSales =

Opening and naming a variable

CALCULATE([Sales],

Modify (calculate) the results of the Sales measure
Keep in mind, that sometimes, the way the Sales measure is written may impact the result of your new measure (ex. if Sales was to only return data for a given product, you cannot expect it to now include additional products in the new measure unless added by another method)

FILTER(
ALLSELECTED(‘Dates’),

Filter the Dates table to return ALLSELECTED rows in the Dates table.

Remember the definition of ALLSELECTED: returns all the rows in a table, or all the values in a column, ignoring any filters applied inside of the query, but keeping those that come from outside. So, at this stage you have ALL dates, except those filtered out by a visual filter.

‘Dates’[Date] <= MAX(‘Dates’[Date])))

Now, you are returning to the FILTER listed outside of the ALLSELECTED, and adding a filter on the dates, making them less than or equal to the largest filtered date. (This allows for the running total behaviour of the measure)

RETURN

Return to the primary measure, and prepare to use the variable defined above

IF( ISBLANK([Sales]),
BLANK(),

If the result of the sales measure is blank, then return blank

CumulativeSales)

If the result of the sales measure is NOT blank, then return the CummulativeSales variable

So, returning all dates is the expected behaviour, unless you add in the IF logic here. Another easy way to accomplish this would be to add the IF logic to the visual filter instead (using the filter pane, add Sales to the table visual, and filter out blanks there). The downside to this is you would have to do it in every visual using this measure, and if another measure didn’t return a blank, but Sales did - you would have gaps in the logic for the other measures.

[patrickcox]

Thanks for your reply. So it sounds like the IF(ISBLANK()) logic is the best solution. I appreciate your time!

[AntrikshSharma]

@patrickcox No, ISBLANK is not the right solution because then Running Total will be blank for days when there is no transaction.

Use this, it is super optimized.

VAR MaxDate = SELECTEDVALUE ( Dates[Date] )
VAR LastDateWithSales =  
    CALCULATE ( MAX ( Sales[Order Date] ), REMOVEFILTERS ( Dates ) )
VAR GroupingByDates = ISINSCOPE ( Dates[Date] )
RETURN
    IF ( 
        MaxDate <= LastDateWithSales && GroupingByDates,
        VAR DatesWithSales = 
            CALCULATETABLE ( 
                ADDCOLUMNS ( 
                    SUMMARIZE ( Sales, Dates[Date] ),
                    "@Sales", [Total Sales]
                ),
                ALLSELECTED ( Dates[Date] )
            )
        VAR PreviousDates = 
            FILTER ( DatesWithSales, Dates[Date] <= MaxDate )
        VAR Result = 
            SUMX ( PreviousDates, [@Sales] )
        RETURN Result,
        IF (
            NOT GroupingByDates,
            CALCULATE ( [Total Sales], ALLSELECTED ( Dates ) )
        )
    )