Filter with AllEXCEPT and two Selection Dates Not working as Excepted

[manosjxmaah]

Hello!

I have a page that contains two dropdown filters and a matrix. The purpose of the page is for the user to select a year in one filter and another, different, year in the other filter. Afterwards, I calculate the percentage difference of a measure in the matrix.

My model is this:

The one filter is on DateTable and the other on CompareDates.

My matrix contains a hierarchy on the 04 Reg table and a hierarchy on the DateTable (uQuarterText and then uMonthName):

I want to calculate the average days a month has (which is contained in MonthTable) in the chosen year of CompareDates, but only when Avg Availability in Month > 0 (which is contained in Units table).

The code I am using is this, but all I get is a matrix with the same values all over:

VAR avgDaysTest = 
        CALCULATE(
            AVERAGE(MonthTable[Days]),
            REMOVEFILTERS(DateTable[Year With Current]),
            REMOVEFILTERS(DateTable[uYear]),
            compYear = MonthTable[_Year],
            FILTER(ALLEXCEPT(Units, DateTable[QuarterText], DateTable[uQuarter], DateTable[uMonthName], DateTable[uMonth],'04 Reg'[Reg], '04 Reg'[Abbr Group],'04 Reg'[Jet-Prop]), 
            Units[Avg Availability in Month] > 0 && compYear = Units[_Year])
         )​

I have also tried removing the ALLEXCEPT part but then I get a blank matrix.

When I remove the FILTER part altogether, I get a matrix with values, but not filtered correctly of course.

In other measures where I don't need to remove the effects of DateTable, the below code works fine:

CALCULATE(
        AVERAGE(MonthTable[Days]),
        FILTER(Units, Units[Avg Availability in Month] > 0)
    )

 Here is a sample pbi file.

Also some screenshot of some data and what i want the outcome to be. I have a simplified version with only reg in the rows of the matrix:

Desired Outcome:

What is wrong with my DAX?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[manosjxmaah]

Hello,

I added screenshots and a sample pbi file in topic.
Here is a link to download the sample file.

If something else is needed please inform me.