Forum Discussion
Post PartisanMeasure for a total count by month
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!
😁😁
Solution SageHey 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.
Post PartisanHi 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,
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