Waterfall Chart with measures

[Hamdan1234]

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

[speedramps]

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]

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 · Accepted solution]

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!