Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
campelliann
Post Patron
Post Patron

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.