DAX formula to get cumulative sales with SELECTEDVALUE

[Anonymous]

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! 

[johnt75 · Accepted solution]

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