How to filter table on a calculated balance sheet date?

[TheoM]

Hello all,

I have an issue that should be quite easy to solve, but i haven't found the solution yet. I hope someone can help me with this.

My data model consists of several tables, some are amounts per month (such as turnover) and some are balances (such as amount of stock as per balance sheet date).

I want to add a slicer so I can select several months and I want to retrieve the opening balance amount from my stock table. My date table contains an opening balance column (first of month minus 1), so it is easy to create a measure that calculates the opening balance date for the selected period of several months. However, i cannot use this measure to filter my stock table (this table contains stock per end of each month): when I use this measure as filter in a calculate function, the result is an error: A function CALCULATE has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Does anyone have a solution for me?

Best regards,

Theo

[v-yuezhe-msft]

@TheoM,

Could you please post the DAX you use? Meanwhile, please check if the following similar thread helps you.

https://community.powerbi.com/t5/Desktop/DAX-Calculate-function-throws-error-when-using-calculated/t...

Regards,
Lydia

[TheoM]

Helly Lydia,

Thanks for the link you posted, but unfortunately I didn't succeed to solve it. I want to filter my stockbalance table, this table contains the stock amount for each month. If I select a reporting periode for the months january and february, my measure "opening balance date" calculates the opening balance date whick is december 31 of the previous year. So far so good.

Next thing I want to do is filter my stock table by opening balance date. I tried the following: Stock opening balance =  calculate(sum(Stock[Stock amount]);Stock[Date]=Datetable[opening balance date]). At this point I get the following error: A function CALCULATE has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I would be very grateful if you could help me out on this.

Regards, Theo

[v-yuezhe-msft · Accepted solution]

@TheoM,

Use the DAX below instead.

 Stock opening balance =  calculate(sum(Stock[Stock amount]);FILTER(Stock;Stock[Date]=Datetable[opening balance date]))

Regards,
Lydia

[TheoM]

Hi Lydia,

Thanks, that did the job.

Best regards,

Theo