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 water fall chart as on single selection of year it should show the selected year as well as the previous year in water fall char as well like below picture. 

Can anybody help? I need to get it done in couple of hours for my presentation. 

Thanks

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!

3 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    Hi Hamdan1234 

     

    Slicers do what they they say on the tin.  They slice !

    So if you slice your fact table or calendar table related to the fact table by 1 year then it will slice your date only for that year.

     

    The work arround is to copy your calandar as a detatched picklist table.

    Now when the user clicks on the picklist slicer it will not slice the fact data.
    So it still shows evereything.

     

    Then you change the measure only to return data if the fact date is between the picklist date - 1 year and picklist date.

     

    Now when the user clicks the picklist slicers the graph will show the picklist year and the previous year.


    Remember we dont get paid, we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

    • 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!