Returning Year N and N-1 values in a measure for Waterfall use

[Stiphn]

Dear all,

Basically, my use case could be seen as very similar to this post: https://community.powerbi.com/t5/Desktop/Dynamic-Waterfall-Chart-toggle-between-Budget-Forecast-Budg...

Building something similar, I am stuck at the LY vs CY comparison.

I am using a P&L Data table with Value, Date, Reporting types. I have a Date table with a relation ship to P&L Data.

Trying to return a measure with data from a month in year N and N-1, I managed to get to this code

Selected Waterfall Value = 
CALCULATE( 
     SUM('P&L Data'[YTDAmountEUR]),  
     FILTER(
          ALL('Calendar'[Year]),
          'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year]) || 'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year])-1),
      EXCEPT(CALCULATETABLE(VALUES('P&L Data'[Reporting Type])),{"B","RF3","RF8"}))
  

This however return a single value for Year N, which combine N and N-1 value.
Can you please help me understand what I am doing wrong?

At the link is my sample (there is a lot in it as I also plan to include YTD/MTD switch sorry for the clutter): https://1drv.ms/u/s!Anpv9-Qk39aJga1xddadQo8PLggBVQ?e=Ef9Vou

Many thanks!

[Icey · Accepted solution]

Hi @Stiphn ,

In which visual would you like to use this measure? I find that the visualization in the image below is not affected by "Year" slicer.

If you just want to use in this visual, try this:

Icey - Selected Waterfall Value = 
VAR SelectedYear_ =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        SUM ( 'P&L Data'[YTDAmountEUR] ),
        'Calendar'[Year] = SelectedYear_,
        EXCEPT (
            CALCULATETABLE ( VALUES ( 'P&L Data'[Reporting Type] ) ),
            { "B", "RF3", "RF8" }
        )
    )

If you want to use it in a filtered visual, try this:

Icey - Selected Waterfall Value 2 =
VAR MaxFactYear_ =
    CALCULATE ( MAX ( 'P&L Data'[Year_] ), ALL ( 'Calendar'[Year] ) )
VAR FactYear_ =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    IF (
        MaxFactYear_ <= MAX ( 'Calendar'[Year] )
            && MaxFactYear_ >= FactYear_ - 1,
        CALCULATE (
            SUM ( 'P&L Data'[YTDAmountEUR] ),
            ALL ( 'Calendar'[Year] ),
            EXCEPT (
                CALCULATETABLE (
                    VALUES ( 'P&L Data'[Reporting Type] ),
                    ALL ( 'Calendar'[Year] )
                ),
                { "B", "RF3", "RF8" }
            )
        )
    )

Best Regards,

Icey

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

[Stiphn]

Thanks, it is working perfectly

[Syndicate_Admin]

Many thanks it works as intended!

[amitchandak]

@Stiphn , Try like

Selected Waterfall Value =
var _max = maxx(allselected('Calendar'), 'Calendar'[Year])
var _min =_max -1
CALCULATE(
SUM('P&L Data'[YTDAmountEUR]),
FILTER(
ALL('Calendar'[Year]),
'Calendar'[Year]=<= _Max && 'Calendar'[Year] >= _min ),
EXCEPT(CALCULATETABLE(VALUES('P&L Data'[Reporting Type])),{"B","RF3","RF8"}))