Forum Discussion

King8James's avatar
King8James
Regular Visitor
4 years ago
Solved

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,th...
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    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.

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
King8James
Regular Visitor
4 years ago

Thanks it works !!!