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/...
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
    )

     

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
)