Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm new to DAX. I hope you can help?
I have a table with start date and end date columns. I would like to add a column that counts how many times the start date from other rows in the table appear between the start date and end date of each row and if the start date doesn't appear, it then checks if the end date appears between the start and end date in each row.
Is the formula for this? It basically an overlap of dates and times
Thanks
Joe
Start Date End Date Count
01/01/2018 00:00:00 01/01/2018 01:45:00 4
01/01/2018 00:00:00 01/01/2018 01:45:00 4
01/01/2018 00:00:00 01/01/2018 01:45:00 4
01/01/2018 01:00:00 01/01/2018 02:45:00 5
01/01/2018 02:00:00 01/01/2018 03:45:00 2
Solved! Go to Solution.
@Anonymous
Try this Column
Count Column = COUNTROWS ( FILTER ( table1, OR ( [Start Date] >= EARLIER ( [Start Date] ) && [Start Date] <= EARLIER ( [End Date] ), [End Date] >= EARLIER ( [Start Date] ) && [End Date] <= EARLIER ( [End Date] ) ) ) )
Hi @Anonymous
Hello Joe
You can use this revised formula
Column 2 = COUNTROWS ( FILTER ( table1, AND ( OR ( [Start Date] >= EARLIER ( [Start Date] ) && [Start Date] <= EARLIER ( [End Date] ), [End Date] >= EARLIER ( [Start Date] ) && [End Date] <= EARLIER ( [End Date] ) ), [ID] = EARLIER ( [ID] ) ) ) )
@Anonymous
Try this Column
Count Column = COUNTROWS ( FILTER ( table1, OR ( [Start Date] >= EARLIER ( [Start Date] ) && [Start Date] <= EARLIER ( [End Date] ), [End Date] >= EARLIER ( [Start Date] ) && [End Date] <= EARLIER ( [End Date] ) ) ) )
Hello Zubair
If you have the time,
I would like to show in another column the same result, but counting with the ID within the timeframe. Basically the formula, but showing the filterd results based on the ID
ID Start Date End Date CountAll Dates Count ID
Bag 01/01/2018 00:00:00 01/01/2018 01:45:00 4 2
Bag 01/01/2018 00:00:00 01/01/2018 01:45:00 4 2
Hat 01/01/2018 00:00:00 01/01/2018 01:45:00 4 1
Hat 01/01/2018 01:00:00 01/01/2018 02:45:00 5 2
Hat 01/01/2018 02:00:00 01/01/2018 03:45:00 2 2
Kind regards
Joe
Hi @Anonymous
Hello Joe
You can use this revised formula
Column 2 = COUNTROWS ( FILTER ( table1, AND ( OR ( [Start Date] >= EARLIER ( [Start Date] ) && [Start Date] <= EARLIER ( [End Date] ), [End Date] >= EARLIER ( [Start Date] ) && [End Date] <= EARLIER ( [End Date] ) ), [ID] = EARLIER ( [ID] ) ) ) )
Hi Zubair
It worked perfectly, thank you so much!
Kind regards
Joe
Thank you Zubair
You have thought me two lessons.
1. The formula
2. To ask for help sooner 🙂
Kind regards
Joe
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |