DAX: Running count distinct within selection

0
votes

I have a data table in my Power Query model looking like this:

date customer_id category1 category2
1 1 1 1
2 1 1 2
3 4 2 3
1 2 2 2
2 2 1 3
3 4 1 1
1 2 2 2
2 3 2 1
3 5 1 3
1 1 1 1
2 3 2 3
3 6 2 2
1 2 1 3
2 2 1 2
3 5 1 1

I have a calculated meassure that counts distinct customer_id:

Distinct_cust:=DISTINCTCOUNT(Table1[customer_id]))

This meassure works great in pivot table - it calculates corectly for all intersentions of any and all fields I add to the pivot and all subtotals.

Now I want to have a meassure that will work within the scope of given intersection but takes into account previous date values - it should return running count distinct of custeomer_id, but only for values of days that are incurrent pivot table.

Right now I have something like this that ALMOST works correctly:

Cumulative_Distinct_cust:=CALCULATE (
    [Distinct_cust];
    FILTER (
        ALL ( 'Table1'[date] );
        'Table1'[date] <= MAX ('Table1'[date]) 
    )
)

This returns corectly running count distinct but only if all dates are not filtered out in my pivot table. As soon as I remove day nr 1 from pivot it still show running count distinct for day 2 and 3 af if day 1 was also taken into account:

Example of problem im getting

Brute force solution I came up with and I'm looking to replace is to add manually day filter inside FILTER statement which will limit date to only day 2 and 3:

Cumulative_Distinct_cust:=CALCULATE (
    [Distinct_cust];
    FILTER (
        ALL ( 'Table1'[date] );
        AND(
              'Table1'[date] >= 2; <-- added this to filter
              'Table1'[date] <= MAX ( 'Table1'[date]) 
    )
))

fixed version

(You can see difference in column labeled as 1)

but obviously this is ugly and cumbersome to change every time I want to change date ranges. Is there any way to pass current available values of date field for purpose of this calculation?

1
exceldaxpowerquery

1 Answers

0
votes

you can do it like this:

Cumulative_Distinct_cust:=VAR _date = MAX(Table1[date])
RETURN
CALCULATE (  [Distinct_cust];
    FILTER (
        ALL ( 'Table1'[date] );
        'Table1'[date] <=  _date 
    )
)