Forum Discussion
DAX "Puzzle" - Intersect Dates with Iterators- Help
Hi,
So I am trying to do the following exercise. I have one Table with my projects which encompasses beginning date and end date:
Project Table
| Project Name | Beginning Date | End Date |
| Project 1 | 30jun 2021 | 31 dec 2021 |
| Project 2 | 1 january 2021 | 31 Dec 2021 |
I am doing a visual chart with a Date Hierarchy on X axis ( I use a different table to filter - the Calendar Table). And I basically want to sum the number of days worked in that particular filter context for all the projects.
Example1 : the graph shows 2021 in X axis: the result is 184 days (half year) Project 1+ 365 days for Project 2 = 549 days worked
Example 2: the graph shows january onn x axis: the result is 31 days worked from Project 2 (Project 1 does not have any work on January).
So I am basically intersecting for each project the list of dates between beginning and end, and the current filter context.
I almost managed to make this work like this - but it only works with the project ID in the visual which I want to avoid - the problem seems to be in the Max date, but I dont know how to do the count of intersect row by row, than sum....
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
2 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.
- campelliann
Post Patron
Thank you JiHwan Kim. This will do... I will still try to work a little bit my "solution" if it doesnt work, I'll use yours which definitely works.