Measure for a total count by month

[RichOB]

Hi, I'm looking to get the count of total dogs who were actively in a kennel each month in 2024. Using the table, what measure would give me the figures below, please? Also, I need to add a year and month filter, but if I make a calendarauto table and connect the date to the Start_Date or End_Date, it will give me incorrect figures. How would this work, please?

January 9

February 9

March 8

April 8 

May 7

June 7

July 5

August 5

September 3

October 3

November 3

December 3

Dog	Start_Date	End_Date	Support_Status
Max	01/01/2024	20/05/2024	Closed
Molly	01/01/2024		Open
Alba	01/01/2024	20/07/2024	Closed
Chester	01/01/2024	20/08/2024	Closed
Birdie	01/01/2024		Open
Alf	01/01/2024	20/06/2024	Closed
Biffy	01/01/2024		Open
Bob	01/01/2024	20/02/2024	Closed
Tess	01/01/2024	20/04/2024	Closed

Thanks

[Anonymous]

Hi @RichOB,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @RichOB,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @RichOB,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[RichOB]

Hi @freginier thanks so much for your help. The measures are showing as blank and I think it's to do with the calendar.

I think I'm falling at the first hurdle here because the calendar table isn't set up correctly. In the ActiveDogs measure, you reference the MAX('Calendar'[Date] + MIN('Calendar'[Date]. Is this coming from the Calendarauto calendar? If so, how do I connect that to the Dogs table, please?

[freginier]

Hey RichOB

The key point here is that the MAX(Calendar[Date]) and MIN(Calendar[Date]) are referencing the Calendar table, but it looks like the issue lies with how this table is set up and connected to the Dogs table.

Ensure that the Calendar Table is Correctly Set Up:

• The Calendar table should contain a list of dates covering all the possible dates in the Start_Date and End_Date fields from the Dogs table.

• You can create the Calendar table in Power BI using DAX like this

Calendar = CALENDAR(MIN(Dogs[Start_Date]), MAX(Dogs[End_Date]))

• You need to create a relationship between the Calendar table and the Dogs table.

• The Calendar[Date] column should be related to both the Dogs[Start_Date] and Dogs[End_Date] columns.

  • The relationship should be a many-to-one relationship with the Calendar table being the "one" side.

  • You can create two relationships, one between Calendar[Date] and Dogs[Start_Date], and another between Calendar[Date] and Dogs[End_Date].

If your measure uses MAX(Calendar[Date]) and MIN(Calendar[Date]), you can ensure that these values are coming from the Calendar table for each row context. This will allow the ActiveDogs measure to work correctly.

you can try changing your DAx to this : 

ActiveDogs =
CALCULATE(
COUNTROWS(Dogs),
FILTER(
Dogs,
Dogs[Start_Date] <= MAX(Calendar[Date]) && Dogs[End_Date] >= MIN(Calendar[Date])
)
)

It was probably returning blanks because of the relationships, the Dogs table and the Calendar table. Without this relationship, the MAX(Calendar[Date]) and MIN(Calendar[Date]) were not filtering the Dogs table correctly.

[RichOB]

Hi @freginier thanks so much for your explanation and help so far. I feel like the deeper I go, the more issues pop up. I can connect the Start_Date or End_Date individually Many to One, but when I try to add the 2nd it says:



Any ideas how to fix this? Thanks for all of your help so far!

[Anonymous]

Hi @RichOB,

Power BI detects multiple paths between the 'Dogs' table and the 'Calendar' table that creates an ambiguous relationship. This happens because you're trying to connect both Start_Date and End_Date from 'Dogs' to 'Calendar'.

To resolve your issue: Keep one relationship active and Create the second relationship as inactive.

Regards,

Vinay Pabbu

[freginier · Accepted solution]

Hey there!

You'll need a DAX measure or query that counts the dogs who have overlapping dates within each month of 2024.

You can create a measure like:

ActiveDogs =
COUNTROWS(
FILTER(
Dogs,
(Dogs[Start_Date] <= MAX(Calendar[Date]) && Dogs[End_Date] >= MIN(Calendar[Date]))
)
)

This counts the number of dogs whose start and end dates overlap with the selected month.

for you second question: 

If you have a Start_Date and End_Date field and you're using a Calendar table, applying a Year and Month filter might not yield correct results. A common solution is to use a DAX measure that explicitly checks if the date range is between the Start_Date and End_Date.

Here’s an approach to work with the date filter correctly:

ActiveDogsPerMonth =
CALCULATE(
[ActiveDogs],
FILTER(
Calendar,
Calendar[Date] >= MIN(Dogs[Start_Date]) &&
Calendar[Date] <= MAX(Dogs[End_Date])
)
)

Hope this helps!

😁😁