cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
campelliann
Resolver I
Resolver I

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 NameBeginning DateEnd Date
Project 130jun 202131 dec 2021
Project 21 january 202131 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....

Measure  =
var dates_x_axis= values('Calendar Table [Date])
var dates_per_project= DATESBETWEEN('Calendario Ano (Automatico)'[Date],max('Project Table [Beginning date]),max('Project Table [end date]))

return
SUMX('ProjectTable',countrows(INTERSECT(dates_per_project,dates_x_axis))










1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.

 

Picture1.png

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.

 

Picture1.png

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors