Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX function remove filter

Hi All! Hope doing well,

 

I have an issue in one personal project im working on.

I have a table called Costs which contains period, brand,team code,country, and values. Then i have another one called Team which contains info about the teams such as employee name, country, brand, team code, region,etc.

 

Im creating a table that have country, employee name, office, brand, and one measure as columns, now i need another measure that calculates costs over the past 3 months for the brand that the employee is working now, lets say one row is employee A, brand XY, country B, i need a measure to calculate the XY total costs, and another one to calculate XY costs for country B. Is it possible?

 

Let me know if anything missing.

 

Thanks!

  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Anonymous , You can try using measures

     

    TotalCostsBrand3Months =
    VAR CurrentBrand = SELECTEDVALUE(Team[brand])
    VAR CurrentDate = MAX(Costs[period])
    RETURN
    CALCULATE(
    SUM(Costs[values]),
    Costs[brand] = CurrentBrand,
    Costs[period] >= EDATE(CurrentDate, -3)
    )

     


    TotalCostsBrandCountry3Months =
    VAR CurrentBrand = SELECTEDVALUE(Team[brand])
    VAR CurrentCountry = SELECTEDVALUE(Team[country])
    VAR CurrentDate = MAX(Costs[period])
    RETURN
    CALCULATE(
    SUM(Costs[values]),
    Costs[brand] = CurrentBrand,
    Costs[country] = CurrentCountry,
    Costs[period] >= EDATE(CurrentDate, -3)
    )

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Anonymous , You can try using measures

     

    TotalCostsBrand3Months =
    VAR CurrentBrand = SELECTEDVALUE(Team[brand])
    VAR CurrentDate = MAX(Costs[period])
    RETURN
    CALCULATE(
    SUM(Costs[values]),
    Costs[brand] = CurrentBrand,
    Costs[period] >= EDATE(CurrentDate, -3)
    )

     


    TotalCostsBrandCountry3Months =
    VAR CurrentBrand = SELECTEDVALUE(Team[brand])
    VAR CurrentCountry = SELECTEDVALUE(Team[country])
    VAR CurrentDate = MAX(Costs[period])
    RETURN
    CALCULATE(
    SUM(Costs[values]),
    Costs[brand] = CurrentBrand,
    Costs[country] = CurrentCountry,
    Costs[period] >= EDATE(CurrentDate, -3)
    )