DAX function remove filter

[Anonymous]

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 · Accepted solution]

@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)
)