Forum Discussion

Hamdan1234's avatar
Hamdan1234
Helper III
4 years ago
Solved

Waterfall Chart with measures

Hello, I have a year filter which make the graph look like below picture. I have made the year filter to single select now it shows one year of data as below. But I want to see the wat...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Hamdan1234 ,

     

    Create a date table as below:

    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    Then create a measure as below:

    Measure =
VAR _mindate =
    MINX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= _mindate
            && MAX ( 'Table'[Date] ) <= _maxdate,
        SUM ( 'Table'[Value] )
    )

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

Hamdan1234's avatar
Hamdan1234
Helper III
4 years ago

Hello speedramps ,

Can't we create a slicer that has selection value of both selected year and previous year? i guess it should be possible in other Waterfall chart available in store because they are taking more than one values while default chart takes only one value for the Value field. 

v-kelly-msft's avatar
v-kelly-msft
Community Support
4 years ago

Hi  Hamdan1234 ,

 

Create a date table as below:

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

Then create a measure as below:

Measure =
VAR _mindate =
    MINX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
RETURN
    IF (
        MAX ( 'Table'[Date] ) >= _mindate
            && MAX ( 'Table'[Date] ) <= _maxdate,
        SUM ( 'Table'[Value] )
    )

And you will see:

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!