Dax Calculate formula cannot remove the impact of slicer

[violet11_6]

Hi.

I have a tab "PnL Summary" that contains a slicer [Year] that controls the actual financial summary of this tab as well as the detailed actual financial results in another tab "Details".  However, in the other tab "Details", I need to display an additional metric [Year-on-year 1H delta %] which pulls the difference between the first 6 months financial results of 2 years. As such, I need a new measure to return the 1H results of the previous year before I could work out the year-on-year difference. Note: slicer [Year] will control the actual financial results of both tabs but the financial results of 1H should always only pull the first 6 months results, regardless of the year selected under the slicer [Year]. 

For whatever reason, my Dax formula below does not seem to work (after narrowing down each filter under this formula):
PY_1H_Revenue = Calculate (sum(Fact_PnL[Value]), Fact_PL[Year] = "2022") {since my staff has set the year up as text instead of number}

I understand that the filter expressions in the CALCULATE formula should override the conditions selected under slicer. 

What else should I check for that I have not checked resulting in this error? Thanks. 

[violet11_6]

Hi,

No, the date table is not marked as a date table (I infer through the dropdown [mark as date table] as unselected).

Thanks.

[bcdobbs]

Try marking it as a date table. Would hope that fixes.

[violet11_6]

Hi,

There seem to be 3 other filters/slicers that are affecting my Dax formula. How can I find out the source of these slicers/filters?

Thanks. 

[bcdobbs]

Try creating a new page with a simple visual for your measure to see if it works in isolation. 

If it works there then have a look at View >> Sync Slicers and see if anything is being synced from another page.

Failing that build the page back up one visual at a time (copy and paste) until it "breaks"

[bcdobbs · Accepted solution]

I think the issue is that in your calculate you're using Fact_PL[Year].

Do you have the same Year filter in a date table or separate dimenesion? If you use that it should work.

The issue is that when you filter a fact table directly all the columns end up in the filter context and you're only overiding the year one.

[violet11_6]

Hi,

Thanks. You're correct. But now, I seem to face the same issue with the months, even though I tried filtering the month from a different dimension, just as I did with the year. 

[bcdobbs]

Is month on a date table? Is the date table marked as a date table? (Right click on the table and it'll give you the option).

Failing that I'd need to see a bit more detail. Are you able to share a pbix file with some demo data?