Filter measure based on dimension

[Scholes]

Hi,

I have th following measure

 Loan = CALCULATE(SUM(Balans[Bedrag VV]),Balans[Administratie ID]=3070,Balans[Rekening ID] IN {"053037"}) 

which returns in 2024 (period 01/01/2024 - 31/07/2024) values split by dimension x values, it is a matrix.

Now I want to filter previous periods < 01/01/2024 on exactly the same dimension x values as those with values in 2024.

So in general in earlier years I want to fix my dimension x  and show the related values of the measure. 

When there are other dimension x values which didn't have a value in 2024 then these should be excluded in the prior year selection.

Thank you.

[Scholes]

Thank you this was wat I was looking for!!!  Did you use a AI tool for this solution? 

[bhanu_gautam · Accepted solution]

@Scholes , You can try below method

Create a table that contains the dimension x values with values in 2024.
Use this table to filter the previous periods.

DAX
// Step 1: Create a table with dimension x values that have values in 2024
DimXValuesIn2024 =
SUMMARIZE(
FILTER(
Balans,
Balans[Date] >= DATE(2024, 1, 1) && Balans[Date] <= DATE(2024, 7, 31)
),
Balans[DimensionX]
)

// Step 2: Calculate the measure for previous periods using the filtered dimension x values
LoanPreviousPeriods =
CALCULATE(
SUM(Balans[Bedrag VV]),
Balans[Administratie ID] = 3070,
Balans[Rekening ID] IN {"053037"},
Balans[Date] < DATE(2024, 1, 1),
Balans[DimensionX] IN DimXValuesIn2024
)