Forum Discussion

campelliann's avatar
campelliann
Icon for Post Patron rankPost Patron
4 years ago
Solved

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))










2 Replies

    • campelliann's avatar
      campelliann
      Icon for Post Patron rankPost 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.