topic Re: DAX formula to get cumulative sales with SELECTEDVALUE in DAX Commands and Tips

DAX formula to get cumulative sales with SELECTEDVALUE

Anonymous — Wed, 24 May 2023 12:21:37 GMT

Hello,

I want to create a barchart that will show the cululative total sales over a period of selected months by the user. In the X-axis will be the month(s) and in the Y-axis the sales measure.

My struggling point is that in Y-axis I use so far a measure that includes a SELECTEDVALUE function, since I have a filter with 3 different Sales metrics:

Metric = VAR MySelection = SELECTEDVALUE ( 'Measures'[Selection], "Sales" ) RETURN SWITCH ( TRUE (), MySelection = "Value", [Sales Value], MySelection = "Items", [Sales Items], MySelection = "Units", [Sales Units], blank() )

Any idea how can I convert my DAX code above, in order to get cumulative Sales?

Thanks!

Re: DAX formula to get cumulative sales with SELECTEDVALUE

johnt75 — Wed, 24 May 2023 13:03:36 GMT

Firstly, rather than your current solution you should look into using a fields parameter. they are specifically designed to allow the user to choose which metric to show.

To solve the problem of being able to toggle between accumulated or not you could use a calculation group with 2 items. The first item would just call SELECTEDMEASURE(), so returning the non-cumulative values. The second calculation item could be

Cumulative in period = VAR StartDate = CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR EndDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( SELECTEDMEASURE (), DATESBETWEEN ( 'Date'[Date], StartDate, EndDate ) ) RETURN Result