Forum Discussion
Post PartisanMeasure for a total count by month
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
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!
😁😁
8 Replies
freginierSolution Sage
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!
😁😁
RichOBHi 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!
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 PabbuHi 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 PabbuHi @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
