Dynamic sales based on slicer

[King8James]

Hello,

I need to create a measure that gives the sales of the selected scenario (selected by users from slicer) then adds previous sales (called "fix"), for example if scenario starts from may,then "fix" should end at april and so on, i created the following measure 

new sales= 

VAR sce = SELECTEDVALUE(forecast[Alternative])


return



CALCULATE(SUM(data[Sales]),FILTER(data, data[Alternative]=sce || data[Alternative]="FIX" 

))

 

 but it gives me overlp like this 

what i want is this

can someone help me with that please

here is the data

[v-jianboli-msft · Accepted solution]

Hi @King8James ,

Please try:

new sales =
VAR sce =
    SELECTEDVALUE ( forecast[Alternative] )
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Alternative] = sce || 'Table'[Alternative] = "FIX" )
    )
VAR _d =
    CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), [Alternative] = sce )
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Month] ) ) >= MONTH ( _d )
            && MAX ( 'Table'[Alternative] ) = "FIX",
        BLANK (),
        _a
    )

Final output:

Best Regards,

Jianbo Li

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

[King8James]

Thanks it works !!!

[Arul]

@King8James ,

Please provide the data in a accessible format so that it would be helpful for us to solve.

Thanks,

Arul

[King8James]

Month	Alternative	Sales
01/06/2022	VAR 2	100
01/07/2022	VAR 2	100
01/08/2022	VAR 2	100
01/09/2022	VAR 2	100
01/10/2022	VAR 2	100
01/11/2022	VAR 2	100
01/12/2022	VAR 2	100
01/01/2023	VAR 2	100
01/02/2022	FIX	100
01/03/2022	FIX	100
01/04/2022	FIX	100
01/05/2022	FIX	100
01/06/2022	FIX	100
01/07/2022	FIX	100
01/05/2022	VAR 1	100
01/06/2022	VAR 1	100
01/07/2022	VAR 1	100
01/08/2022	VAR 1	100
01/09/2022	VAR 1	100
01/10/2022	VAR 1	100
01/11/2022	VAR 1	100
01/12/2022	VAR 1	100
01/01/2023	VAR 1	100