FILTER in DAX measure not working properly -- when there are 2 filters

[dancarr22]

Hello,

I am using Power BI/DAX to calculate linked returns for a portfolio.

We have 2 different return fields -- one for daily return and one for monthly returns.

Theoretically, they should return the same value.

The DAX formulas are almost exactly the same except the MTD has an additional FILTER -- where IsMTD=TRUE

THIS WORKS

msr_LinkedReturn_Daily = VAR DATECOL= MAX(AccountReturn[AccountReturn_AsOfDate])
RETURN (CALCULATE(
        PRODUCT(
          AccountReturn[calc_Return_Daily]
            ),
            FILTER(
               AccountReturn,
                AccountReturn[AccountReturn_AsOfDate]<=DATECOL )
               )-1)

THIS DOES NOT WORK

msr_LinkedReturn_MTD = VAR DATECOL= MAX(AccountReturn[AccountReturn_AsOfDate])
RETURN (CALCULATE(
        PRODUCT(
          AccountReturn[calc_Return_MTD]
            ),FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]=TRUE),
            FILTER(
               AccountReturn,
                AccountReturn[AccountReturn_AsOfDate]<=DATECOL )
               )-1)

 The only real difference between these 2 formulas (besides a different field value being used) is the MTD has 2 filter fields -- the daily only has one.  The additional filter in MTD is: FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]=TRUE),

The MTD does sometimes work when I only rollup for a few portfolios -- but when I select more than 3 or 4 everything gets set to -100%.

Any help you can offer is greatly appreciated.

Thanks,

Dan

[v-yuezhe-msft]

@dancarr22,

What is the data type of IsLastDayOfMonth field? Is it text or True/False? You need to change the additional FILTER formula to the following if it is text:

FILTER(AccountReturn,AccountReturn[IsLastDayOfMonth]="TRUE")

If the above step doesn't help,  please share sample data of the AccountReturn table here.

Regards,
Lydia

[dancarr22 · Accepted solution]

Hi Lydia,

Thanks for your response.

The field was a boolean (true/false).  I found the error was in setting the variable at the start of the function.  That was not required as that filter was already applied by defualt.

So, this issue has been resolved.  Was user error.

Thanks,

Dan