Forum Discussion
Counting rows between dates in different columns
Hi there,
I’m trying to track the number of active members over time by using two different date columns within a single fact table:
- [Valid From]
- [Cancelled At]
FYI The membership fact table is related to a calendar table with an active relationship between [Valid From] and [Calendar Date] and inactive relationships between a couple of other dates, including [Cancelled At].
I’m still new to Power BI so my initial thought was I would need to create a calculated column alongside the date range and have each row evaluate the number of rows within the membership fact table which meet the following criteria:
[Calendar Date] >= [Valid From] && <= [Cancelled At]
OR
[Calendar Date] >= [Valid From} && [Cancelled At] = BLANK.
This would then allow me to select any date from my calendar table and return the active membership count at that time. The only problem is I haven’t been able to figure out how to do a count between dates across two different columns as well as nesting the OR statement to cater for memberships that are still active.
Any help in how to do this would be extremely useful!
Kind regards
Michael
Hi MichaelBauld ,
Please add a [index] column for 'Member_Warehouse' table and change the relationship status to Active.
Then create a measure.
Current Employees = CALCULATE ( COUNTX ( FILTER ( 'Member_Warehouse', 'Member_Warehouse'[validfrom] <= MAX ( 'Date Table'[Date] ) && ( ISBLANK ( 'Member_Warehouse'[End Date] ) || 'Member_Warehouse'[End Date] > MAX ( 'Date Table'[Date] ) ) ), ( 'Member_Warehouse'[Index] ) ), CROSSFILTER ('Member_Warehouse'[validfrom],'Date Table'[Date], NONE) )Drag the measure and [day] columns together into the visual and check the results.
Best Regards,
Community Support Team_GaoIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
6 Replies
MichaelBauld , Are looking something similar to active employee in this blog
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.amitchandak - thank you for the really helpful HR related post. It is exactly what I am trying to accomplish. However, I have input what I beelive is the correct DAX but am getting some strange outputs. The following is what I drew from your blog post:
Member Count = CALCULATE (COUNTX (FILTER(Member_Warehouse,Member_Warehouse[validfrom]<=MAX('Calendar MASTER'[Date]) && Member_Warehouse[End Date]>MAX('Calendar MASTER'[Date])),Member_Warehouse[validfrom]))
I modified the expression in your blog by removing the filter for BLANK end dates. The [End Date] column is now a calculated column which pulls its value from one of two different date fields within the fact table ([cancelled at] and [valid to]).
"End Date", each if [cancelledat] = null then [validto] else [cancelledat]),
To cut a long story short, the calculated column means there are no blank values.
However, the counts I get back are very wrong. Each date should be returning in the region of 170-200k of rows meeting the criteria:
I'm not sure if I have made an error in modfiying the expression, or whether there is something else I am not picking up, but if you have any thoughts I would be really grateful to hear them!
I'm still struggling to locate the problem of getting a count of all rows between the two selected dates by date. I have pulled some sample data and created a simple model of just the membership table and a date table.
As ever, really appreciate any help the community can provide.
Kind regards
Michael
