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)returnCALCULATE ('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.

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

Post Prodigy

4 years ago

Solved

Modifying cumulative measure

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.

Need Help

v-angzheng-msft
4 years ago
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.
W44_Modifying cumulative measure Sample.pbix49 KB

8 Replies

amitchandak
Super User
4 years ago
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
  Post Prodigy
  4 years ago
  amitchandak Hi, I just tried it and still gives same result in totals and in rows.
v-angzheng-msft
Community Support
4 years ago
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.
W44_Modifying cumulative measure.pbix41 KB
- Justas4478
  Post Prodigy
  4 years ago
  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).
v-angzheng-msft
Community Support
4 years ago
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.

Sample (dummy dataset) data as text, use the table tool in the editing bar

Expected output from sample data

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
  Post Prodigy
  4 years ago
  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
    Community Support
    4 years ago
    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.
    W44_Modifying cumulative measure Sample.pbix49 KB

Forum Discussion

Modifying cumulative measure

8 Replies

Related content

Modifying Cumulative Measure

Cumulative Difference from Another measure

Cumulative Measure

Cumulative Total

Transform measure in cumulative measure