Calculate Unique days in Month (for DateTime Column)

[berry195]

Hello, I have a DateTime Column like this: 

I also have a column Month Name:

I want to create a measure to calculate how many unique days per each month. Nonetheless when I write these formulas, they do not work (always shows 31):

and:

This one works:

Could you help explain why my first 2 formulas do not work as expected, I am understanding something wrong.

Thank you a lot!

[rajendraongole1 · Accepted solution]

Hi @berry195 - Both the first two formulas are not considering the month context when calculating distinct days. They’re working over the entire dataset rather than per month, which is why they return the total number of distinct days (or all days) in the dataset rather than for a specific month.

DistinctDaysPerMonth =

CALCULATE(

    DISTINCTCOUNT('Calendar1'[Date]),

    ALLEXCEPT('Calendar1', 'Calendar1'[Month Name])

)

Hope it helps.