Modifying cumulative measure

[Justas4478]

Hi, I have Cumulative sales measure that is working fine for simple cases. But when I apply date filter to table visual in power bi. This measure starts to ignore all the sales that happened before date set in the filter. I tried to use ALLEXCEPT and ALLSELECTED DAX but I cant get it to work. This is the measure. Just to note 'Actuals'[Sales Volume] is measure not a column.

Cumulative Sales volume =

var _min = date(2021,06,01)

return

CALCULATE (

'Actuals'[Sales Volume],

FILTER (

ALLSELECTED ( 'Date' ),

'Date'[Date]<= MAX ( 'Date'[Date] ) && 'Date'[Date]>= _min

))

 This line "var _min = date(2021,06,01)" tells from when sales should be shown in the table. And as you can see it works but the problem is that it as well ignores all the sales before that date.

I would like to be able to filter table to limit amount of rows I need to look at. But the measure sould be able to get the past sales data and calculate them in to the results on the table.

From what I know I think it should be possible.

If there is anything unclear just let me know.

Thank you.

[v-angzheng-msft]

Hi, @Justas4478 

So what you want to achieve is to calculate the running total from the filtered date? Then you only need to modify the ALL function in the above code to the ALLSELECTED function, as follows:

Cumulative Sales volume = 
CALCULATE([Sales Volume],FILTER(ALLSELECTED(Table2),'Table2'[Date]<=MAX('Table2'[Date])))

Result:

If this is not the result you want, could you please posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

@v-angzheng-msft 

1: This is link to sample file. https://we.tl/t-bQ1Q15qP8C 

2: I wrote expected outputs in the sample file, but will write it here as well.

Sales Volume total - 11,812,413

Expected Cumulative sales volume total - 11,810,594

Cumulative sales total should stay 11,810,594 even if table is filtered just to show December mont only.

3: Measure should caculate cumulative sales starting from 01/06/2021 any sales before this date should be ingnored by the measure. Measure should still be able to calculate sales even if table visual is filtered to only show december month for the user. Which is where it fails to work.

Since measure stops calculating any data before december. It should ignore December filter placed by the user but still obey to 01/06/2021 date and calculate sales only starting from this date.

[v-angzheng-msft · Accepted solution]

Hi, @Justas4478 

What you want to achieve is to calculate a cumulative total from a set minimum date to the maximum date in the table, regardless of how the table visual is filtered?
Try to modify your measures as follows:

_Cumulative Sales volume = 
VAR _min =
    DATE( 2021, 06, 01 )
var _max=CALCULATE(MAX('Table1'[Date]),ALL(Table1))
RETURN
    CALCULATE(
        [_Sales Volume],
        FILTER(
            ALL('Table1' ),
            'Table1'[Date] <= _max
                && 'Table1'[Date] >= _min
        )
    )

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

@v-angzheng-msft Looks like it worked. Thank you so much.

[v-angzheng-msft]

Hi, @Justas4478 

Try to create a measure like this:

Cumulative Sales volume = 
CALCULATE([Sales Volume],FILTER(ALL(Table2),'Table2'[Date]<=MAX('Table2'[Date])))

Result:

The measure will not ignore previous sales no matter how you filter the data.

If this doesn't work for you, could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Justas4478]

@amitchandak Hi This solution works. But it as well calculates sales before var _min = date(2021,06,01) date. which are not required for the table and that results in much higer sales numbers than actual. I dont know is it possible to keep the effects of the solution you just wrote and to have as well limitation of this date(2021,06,01).

[amitchandak]

@Justas4478 ,Try with all

Cumulative Sales volume =
var _min = date(2021,06,01)

return
CALCULATE (
'Actuals'[Sales Volume],
FILTER (
ALL( 'Date' ),
'Date'[Date]<= MAX ( 'Date'[Date] ) && 'Date'[Date]>= _min
))

[Justas4478]

@amitchandak Hi, I just tried it and still gives same result in totals and in rows.