Forum Discussion
Dynamic sales based on slicer
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
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.
4 Replies
v-jianboli-msftCommunity Support
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.
Thanks it works !!!
- Arul
Super User
Please provide the data in a accessible format so that it would be helpful for us to solve.
Thanks,
Arul
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
