How to group by on Power BI using DAX

Question

I have two tables in Power BI as follows:

COUNTRIES

COD      COUNTRY
1        BRAZIL
2        ARGENTINA
3        CHILE
4        BRASIL
5        COLOMBIA
6        ARGENTINA
7        URUGUAI

SALES

COD     DATE
1       2021-01-02
2       2021-10-01
3       2019-09-04
1       2018-07-05
7       2019-04-10

There's a relationship between the two tables, on the COD column.

I need to count how many countries (column "COUNTRY" from the table "COUNTRIES") have a status CHURN. It's considered CHURN when their latest order from the table "SALES" is more than 180 days, using today as a reference.

I know that I need to group by the MAX date per country, do a DATEDIFF, and then do a COUNT. I've tried using ALL and SUMMARIZE, but I haven't found a way to solve this problem.

Answer 1

Are you able to add a calculated column to store the max sales date for each country in your COUNTRIES table? Either in Power BI or directly in your database. If so, here's one solution with 2 steps.

Create a MaxSalesDate column in your COUNTRIES table. DAX for a calculated column below:

MaxSalesDate = 
VAR COD = COUNTRIES[COD]

RETURN MAXX(FILTER(SALES, SALES[COD] = COD), SALES[DATE])

Create a measure that counts the number of MaxSalesDate values that are older than 180 days old:

CountCHURN = COUNTX(COUNTRIES, IF(DATEDIFF(COUNTRIES[MaxSalesDate], TODAY(), Day) > 180, 1))