Cumulative distinct count

[Anonymous]

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

[AmiraBedh · Accepted solution]

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

[Anonymous]

@AmiraBedh  thanks for the help here. I just tried that and its still not working properly 

[AmiraBedh]

If possible can you please share your model ?

[Anonymous]

@AmiraBedh  unfortunately I cannot share the model at this time. If I have time i will create a example file 

[AmiraBedh]

Yes please 🙂