DAX Count Customers based on a MTD measure

[SR_2021]

I have successfully created two measures

1) Sales MTD; and

2) Sales Prior Month to date

I want to

a) count how many customers have been sold to so far this month; and

b) count how many customers had been sold to at the same time last month

There is a relationship between Customers (DIM table) and Sales (FACT table)

Can anyone help with this as I have tried various combinations with countrow, counta, countif functions but with no luck.

[SR_2021]

Thanks for this reply - which I think will help me solve a different challenge 🙂

[Adamboer]

To count the number of unique customers sold to so far this month and at the same time last month, you can use the following DAX formulas:

1. To count the number of unique customers sold to so far this month:
   
   Customers This Month =
   DISTINCTCOUNT(Sales[Customer ID], FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY()) && MONTH(Sales[Date]) = MONTH(TODAY())))
   
   
   

   Explanation:

   • The FILTER function filters the sales table to only include sales that occurred in the current month (using YEAR(TODAY()) and MONTH(TODAY()) functions).
   • The DISTINCTCOUNT function then counts the number of unique customers in the filtered sales table.
   1. To count the number of unique customers sold to at the same time last month:
      
      Customers Last Month =
      CALCULATE(DISTINCTCOUNT(Sales[Customer ID]),
      DATESYTD(DATEADD(TODAY(), -1, MONTH)),
      ALLSELECTED(Sales))
      
      

      Explanation:

      • The DATESYTD function returns a set of dates for the same time period as the current year-to-date, but for the previous month.
      • The DATEADD function subtracts one month from the current date.
      • The CALCULATE function then filters the Sales table to only include sales that occurred during this time period.
      • The DISTINCTCOUNT function counts the number of unique customers in the filtered sales table.

      Note: Make sure to replace Sales[Customer ID] with the actual name of your customer ID column in the Sales table.