Cumulative distinct count

hello everyone.

On my dasbhoard I have a slicer for Fiscal Month and its still filtering my DAX and I cannot figure out why.

What I am trying to calculate is the disctint count of Customer IDs where the offers is > = 1. for example I am sliced on Oct 2023 for the fiscal month. This means the distinct count should be cumulative for the current selected FY. Sice Oct 2023 falls under FY24 the data should be doing a distinct count for months July 2023 - Oct 2023 (FYs start in July). why is this not working properly?

Measure =

VAR CurrentFY = MAX('Calendar'[Fiscal Year])

VAR MinMonth = CALCULATE(MIN('Calendar'[Fiscal Month]), ALL('Calendar'), 'Calendar'[Fiscal Year] = CurrentFY)

VAR MaxMonth = SELECTEDVALUE('Calendar'[Fiscal Month])

VAR Results =

CALCULATE(

DISTINCTCOUNT(Customer[CustomerID]),

ALL('Calendar'[Fiscal Month]),

'Calendar'[Fiscal Year] = CurrentFY,

'Calendar'[Fiscal Month] <= MaxMonth,

FILTER(

Customer,

[Total Offers] >= 1

)

RETURN Results

I think the issue with your measure might be related to the way the ALL function is being used, particularly in the context of your slicers.

Try the following :

Measure =
VAR CurrentFY = MAX('Calendar'[Fiscal Year])
VAR MaxMonth = SELECTEDVALUE('Calendar'[Fiscal Month])

VAR Results =
CALCULATE(
    DISTINCTCOUNT(Customer[CustomerID]),
    ALLEXCEPT('Calendar', 'Calendar'[Fiscal Year]),
    'Calendar'[Fiscal Year] = CurrentFY,
    'Calendar'[Fiscal Month] <= MaxMonth,
    FILTER(
        Customer,
        [Total Offers] >= 1
    )
)

RETURN Results

Forum Discussion

Cumulative distinct count

5 Replies

Related content

count distinct

Cumulative Count DAX

Cumulative distinct count

Distinct Count

Distinct count