Weekly customer count in DAX

Question

I have a customer dimension:

Dimcust

| Custnum | StartDate | EndDate |

I want to make a new table, using DAX, that for every week in the last year counts the number of customers (distinct custnum) for each week (meaning that the startdate < week and enddate is null or enddate > week).

However I cant get this to work whatsoever.

What I've tried is the following:

Active Users = Calculate(COUNTROWS(filter(DimCustomer; DimCustomer[StartDate].[Date] > WeeklyKPI[Date];DimCustomer[StartDate].[Date] < WeeklyKPI[Date])))

This gave the error:

A single value for column 'Date' in table 'WeeklyKPI' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Where WeeklyKPI[Date] is a date table with each date in the range I want. If this had worked I've would done the same, just with weeknumbers.

What I want:

Weeknumber | Number of customers
     1                13,430
     2                32,530

user5226582 user5226582 · Accepted Answer · 2017-07-07T13:21:29

You are seeing the error because you tried to compare individual values (DimCustomer[StartDate].[Date]) with multiple (WeeklyKPI[Date]).

If WeeklyKPI[Date] has all days of the selected week, you could change your measure to the following:

Active Users = Calculate(COUNTROWS(filter(DimCustomer; 
  DimCustomer[StartDate].[Date] > MIN(WeeklyKPI[Date]);
  DimCustomer[StartDate].[Date] < MAX(WeeklyKPI[Date]))))

Another option is to create a table with week numbers, WeekNum(Num), and allow the user to select a single value. Your measure would then change to

Active Users = Calculate(COUNTROWS(filter(DimCustomer; 
  WEEKNUM(DimCustomer[StartDate].[Date], 2) = FIRSTNONBLANK(WeekNum[Num]))))

Weekly customer count in DAX

1 Answers