Forum Discussion

berry195's avatar
berry195
Frequent Visitor
2 years ago
Solved

Calculate Unique days in Month (for DateTime Column)

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!



Need Help
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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.

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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.