DAX Feature Inquiry

[Syndicate_Admin]

Hello everyone, I'm having a problem in a personal project that I have that I don't know how to solve.

I have a table called Costs that contains date, brand, team, country, and values, and another called Team that contains all the information related to the teams that is to say team, region, country, brands, etc.

I have a table that contains country, employee, office, brand the employee works for, and a measure that calculates other data per employee.
I need a measure that calculates the costs of the last 3 months for the brand in which the employee works, that is, in the table I have employee A who works in brand XY, for country B, I need the measure to return the total of the costs for brand XY (regardless of country) of the last three months, and another measure that calculates the total costs of the XY brand for country B.

I tried to use sumx and summarize but without success, will anyone know how to do it?

Thank you so much!

Best regards

[Syndicate_Admin]

Good afternoon Yilong,

Thank you very much for the answer, I had been trying something similar but I still don't break everything down. In my table that I have as a visual ends up returning the costs per office, that is, total costs by country, brand, and office, those who work in brand A country XY office B. I only need you to return the data of brand A for all those who work there regardless of country or office, then if the next step should return me brand and country, regardless of the office. Super weird to be honest because it seemed to me that with this type of formula it was going to be simple, so it will be a matter of relationships?

Thanks a lot!

[Anonymous]

Hi @Syndicate_Admin ,

Based on your problem, here are my answers.

1. This measure calculates the total costs for the brand in which the employee works, regardless of the country, for the last three months.

TotalCostsLast3Months = 
CALCULATE(
    SUM(Costs[Values]),
    FILTER(
        Costs,
        Costs[Brand] = SELECTEDVALUE(Employee[Brand]) &&
        Costs[Date] >= EDATE(TODAY(), -3)
    )
)

2. This measure calculates the total costs for the brand in which the employee works, for a specific country, for the last three months.

TotalCostsLast3MonthsCountry = 
CALCULATE(
    SUM(Costs[Values]),
    FILTER(
        Costs,
        Costs[Brand] = SELECTEDVALUE(Employee[Brand]) &&
        Costs[Country] = SELECTEDVALUE(Employee[Country]) &&
        Costs[Date] >= EDATE(TODAY(), -3)
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.