Problematic filter on YTD data for bridges/waterfall

[Anonymous]

Hi,

I am trying to prepare a set of bridges/waterfalls, which would show full years change on one side and change in YTDs (most recent vs previous year).

I have prepared a formula in my calendar lookup, which automaticly sets the YTDs ranges (calculated column, defining days in CY YTD, PY YTD and Other). Then, when I apply this as a filter to the bridges follow situation occurs:

The top charts show correct value for 2018, the bottom right one, shows full 2018 data instead of YTD18. The difference between top and bottom charts is the values fiels. Top ones use just sales value column. The bottom one uses price/volume formula such as this one:

PVC calc =

VAR PriceImpact = CALCULATE(

SUMX(

ADDCOLUMNS(

SUMMARIZE('PBI input', [Calculation_basis], Calendar_Lookup[Year]),

"Price", ([Volume PY]) * ([Price CY] - [Price PY])

),

[Price]

), FILTER(ALL(Calendar_Lookup), Calendar_Lookup[Year] <= MAX(Calendar_Lookup[Year])))

VAR VolumeImpact = CALCULATE(

SUMX(

ADDCOLUMNS(

SUMMARIZE('PBI input', [Calculation_basis], Calendar_Lookup[Year]),

"Volume",

([Volume CY] - [Volume PY]) * ([Price PY])

),

[Volume]

), FILTER(ALL(Calendar_Lookup), Calendar_Lookup[Year] <= MAX(Calendar_Lookup[Year])))

VAR CombinedImpact = CALCULATE(

SUMX(

ADDCOLUMNS(

SUMMARIZE('PBI input', [Calculation_basis], Calendar_Lookup[Year]),

"Combined",

([Volume CY] - [Volume PY]) * ([Price CY] - [Price PY])

),

[Combined]

), FILTER(ALL(Calendar_Lookup), Calendar_Lookup[Year] <= MAX(Calendar_Lookup[Year])))

VAR total = SWITCH(

SELECTEDVALUE('PVC Order'[Order]),

1, PriceImpact,

2, VolumeImpact,

3, CombinedImpact,

PriceImpact + VolumeImpact + CombinedImpact )

RETURN if(ISBLANK(total), 0, total)

Can you help me with this?

[Anonymous]

I went back to old version and now it works correcly, so i dont have the file anymore. We can close the topic.

[v-kelly-msft]

Hi  @Anonymous ，

Glad to hear that ! Can you mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

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

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Could you pls share your .pbix file with us?

Best Regards,
Kelly

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