topic Dynamic sales based on slicer in DAX Commands and Tips

Dynamic sales based on slicer

King8James — Wed, 17 Aug 2022 14:02:49 GMT

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

Re: Dynamic sales based on slicer

Arul — Thu, 18 Aug 2022 08:59:14 GMT

King8James ,

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

Thanks,

Arul

Re: Dynamic sales based on slicer

King8James — Thu, 18 Aug 2022 14:26:51 GMT

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

Re: Dynamic sales based on slicer

v-jianboli-msft — Tue, 23 Aug 2022 02:36:49 GMT

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.

Re: Dynamic sales based on slicer

King8James — Thu, 01 Sep 2022 14:08:59 GMT

Thanks it works !!!