Forum Discussion

Scholes's avatar
Scholes
Frequent Visitor
2 years ago
Solved

Filter measure based on dimension

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.

 

 

 

 

 

 

 

Need Help
Show and Tell
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    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
    )

     

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    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
    )

     

  • Scholes's avatar
    Scholes
    Frequent Visitor
    2 years ago

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