Forum Discussion

genaussie's avatar
genaussie
Frequent Visitor
2 years ago

Waterfall Graph - Issue with single select slicer

Hi All,

I have a monthly waterfall graph (calculating the variance trend) on a page with a monthly snapshot slicer (single selection only). I wanted this particular graph to ignore the slicer, so I introduced the ALL statement and it works as intended:

 

Variance =
CALCULATE(Forecast[Forecast] -
CALCULATE(Forecast[Forecast], PARALLELPERIOD(Forecast[Snapshot Period], -1, MONTH)), ALL('Master List'[Snapshot Period]))

 

When the user adjusts the monthly snapshot slicer (single select), I would like future months to disappear from the waterfall e.g. user selecting Oct-23 results in Nov-23 & Dec-23 months disappearing. In a sense, I want to ignore the single select shapshot slicer so multiple months appear in the trend graph (currently working due to my ALL formula), but ALSO utilise the slicer selection to ignore future months. Is this possible and how?

Cheers

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  genaussie ,
    Based on your description, you want to achieve a waterfall visual object by single-selecting your slicer to show only the values from the selected month and before, and ignore the predicted values after that. You can try the following to see if it meets your requirements:
    First create a new calculated column based on your forecast column

    Table = VALUES('Master List'[Snapshot Period])

    SelectedMonth = SELECTEDVALUE('Table'[Snapshot Period])


    Then modify your original formula to filter out values greater than the selected date.

    Variance = 
VAR SelectedMonth = [SelectedMonth]
RETURN
CALCULATE(
    Forecast[Forecast] - CALCULATE(
        Forecast[Forecast],
        PARALLELPERIOD(Forecast[Snapshot Period], -1, MONTH)
    ),
    FILTER(
        ALL('Master List'[Snapshot Period]),
        'Master List'[Snapshot Period] <= SelectedMonth
    )
)


    If this is not the outcome you were hoping for. Please provide detailed sample data and the results you are hoping for. So that I can help you better. Show it as a screenshot or excel. Please remove any sensitive data in advance.

    Best regards

    Albert He

     

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