Forum Discussion
DateSpan
Good afternoon,
I have a datespan table that I'm trying incorporate into my model. I have my fact table, Phases, Datespan and Projects.
I'm trying to avoid the many to many relationship so I cannot attach my datespan to my fact directly. The objective is to be able to pull in ProjectTitle, PhaseName and Date into the same visual. How can I bridge the datespan table to the fact table and prevent a many to many? Or is there a better approach than what I've described?
Thanks
FactTable
| ID | ProjectID | PhaseId | UserAssignment | PhaseStartDate | PhaseEndDate |
| 1 | 1 | 4624 | 1 | 1/31/2025 | 2/23/2025 |
| 2 | 1 | 4624 | 2 | 1/31/2025 | 2/23/2025 |
| 3 | 1 | 4624 | 3 | 1/31/2025 | 2/23/2025 |
| 4 | 1 | 4624 | 4 | 1/31/2025 | 2/23/2025 |
Projects
| ProjectID | ProjectTitle |
| 1 | Project1 |
| 2 | Project2 |
Phases
| PhaseId | EstStartDate | EstEndDate | PhaseName |
| 4264 | 1/31/2025 | 2/23/2025 | Planning |
DateSpan
| Phaseid | Date |
| 4624 | 1/31/2025 |
| 4624 | 2/1/2025 |
| 4624 | 2/2/2025 |
| 4624 | 2/3/2025 |
| 4624 | 2/4/2025 |
| 4624 | 2/5/2025 |
| 4624 | 2/6/2025 |
| 4624 | 2/7/2025 |
| 4624 | 2/8/2025 |
| 4624 | 2/9/2025 |
| 4624 | 2/10/2025 |
| 4624 | 2/11/2025 |
| 4624 | 2/12/2025 |
| 4624 | 2/13/2025 |
| 4624 | 2/14/2025 |
| 4624 | 2/15/2025 |
| 4624 | 2/16/2025 |
| 4624 | 2/17/2025 |
| 4624 | 2/18/2025 |
| 4624 | 2/19/2025 |
| 4624 | 2/20/2025 |
| 4624 | 2/21/2025 |
| 4624 | 2/22/2025 |
| 4624 | 2/23/2025 |
Hi nchamilton2 ,
You're correct that directly joining the DateSpan table to the FactTable introduces a many-to-many relationship, which Power BI doesn’t handle as cleanly as other analytics tools. To support your scenario generating all dates between each phase’s estimated start and end, while avoiding many-to-many joins the recommended approach is to reshape the model slightly by introducing Phases as a bridge table.
First create a dynamic DateSpan table in Power Query that expands each phase into one row per date using this code:
let Source = Phases, ChangedTypes = Table.TransformColumns(Source, { {"EstStartDate", each Date.FromText(_, "en-US"), type date}, {"EstEndDate", each Date.FromText(_, "en-US"), type date} }), AddDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([EstStartDate], Duration.Days([EstEndDate] - [EstStartDate]) + 1, #duration(1,0,0,0))), ExpandedDates = Table.ExpandListColumn(AddDateList, "Date"), SelectedColumns = Table.SelectColumns(ExpandedDates, {"PhaseId", "Date", "PhaseName"}) in SelectedColumnsAdjust the relationships in the model:
- DynamicDateSpan[PhaseId] → Phases[PhaseId] (One-to-many)
- FactTable[PhaseId] → Phases[PhaseId] (Many-to-one)
- FactTable[ProjectID] → Projects[ProjectID] (Many-to-one)
Use Phases as the central bridge. This lets both the fact table and the generated date list connect indirectly, avoiding many-to-many conflicts.
To show ProjectTitle, you can either create a measure like:
ProjectTitle := CALCULATE ( SELECTEDVALUE ( Projects[ProjectTitle] ), TREATAS ( VALUES ( DynamicDateSpan[PhaseId] ), FactTable[PhaseId] ), FactTable )Or build a calculated table if you need a flattened structure.
This setup should meet your needs: dynamically showing each phase across all active dates, with project info, while maintaining a clean and performant model.
hope this helps, please feel free to reach out for further issue.
Thank you.
12 Replies
nchamilton2 , If you are try to deal with between range. These solutions can help
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUMeasure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMI do not see the many to many thing, which columns connected would result in a many to many in your view?
To what I see, connect the calendar to either the start or the end dat of the project and that's it
Or maybe in your DateSpan the dates would repeat as you have mutiple Phaseid?
In that case please consider for the calendar only the colum dates (and months etc), but nothing else
So data model
Connect The date table to Fact
Connect the Phases table to Fact
Connect the Project Table to fact
Connect the calendar (without any ohase column in it) to Fact
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Thanks for the reply. I'm not able to connect the date table to the fact table because it generates a many to many if I connect it via the phaseid. If I connect via the date from Datespan to Phasestartdate in fact I lose the ability to utilize the spanned dates. I want to be able to return a date for each day for each phase between phasestart and phaseenddate.
Thanks.The date table needs to be connected to the fact table via the date column (and the pahse needs to be eliminated from the date table, that is a date table), the rest will be done via DAX
Hi,
Based on the 4 tables that you have shared, show the expected result very clearly.
Hi nchamilton2 ,
Thank you for reaching out to the Microsoft fabric community forum. Thank you FBergamaschi and amitchandak for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. The model should have One -Many relation between Fact Table and DateSpan between Date and PhaseStartDate for it to show the table with Date, Phase Name and Project Title.Please refer to the following images for your understanding:
output:
I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps you resolve the issue. Please reach out for further assistance.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Thanks for the reply. I'm looking for all dates between the EstPhaseStart and EstPhase End. So if phase start was 1/1/2025 and PhaseEnd was 1/15/2025, I would expect to see
Date Phase
1/1/2025 Planning1/2/2025 Planning
1/3/2025 Planningetc..
It's a slowly changing dimension, I just can't figure out how to incorporate it in Power BI. Other analytics tools, no probablem, but because of the many to many in power bi, it's difficult.
Hi nchamilton2 ,
You're correct that directly joining the DateSpan table to the FactTable introduces a many-to-many relationship, which Power BI doesn’t handle as cleanly as other analytics tools. To support your scenario generating all dates between each phase’s estimated start and end, while avoiding many-to-many joins the recommended approach is to reshape the model slightly by introducing Phases as a bridge table.
First create a dynamic DateSpan table in Power Query that expands each phase into one row per date using this code:
let Source = Phases, ChangedTypes = Table.TransformColumns(Source, { {"EstStartDate", each Date.FromText(_, "en-US"), type date}, {"EstEndDate", each Date.FromText(_, "en-US"), type date} }), AddDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates([EstStartDate], Duration.Days([EstEndDate] - [EstStartDate]) + 1, #duration(1,0,0,0))), ExpandedDates = Table.ExpandListColumn(AddDateList, "Date"), SelectedColumns = Table.SelectColumns(ExpandedDates, {"PhaseId", "Date", "PhaseName"}) in SelectedColumnsAdjust the relationships in the model:
- DynamicDateSpan[PhaseId] → Phases[PhaseId] (One-to-many)
- FactTable[PhaseId] → Phases[PhaseId] (Many-to-one)
- FactTable[ProjectID] → Projects[ProjectID] (Many-to-one)
Use Phases as the central bridge. This lets both the fact table and the generated date list connect indirectly, avoiding many-to-many conflicts.
To show ProjectTitle, you can either create a measure like:
ProjectTitle := CALCULATE ( SELECTEDVALUE ( Projects[ProjectTitle] ), TREATAS ( VALUES ( DynamicDateSpan[PhaseId] ), FactTable[PhaseId] ), FactTable )Or build a calculated table if you need a flattened structure.
This setup should meet your needs: dynamically showing each phase across all active dates, with project info, while maintaining a clean and performant model.
hope this helps, please feel free to reach out for further issue.
Thank you.
Hi nchamilton2 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.Thank you.
Hi nchamilton2 ,
I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.
Thank you.
Hi nchamilton2 ,
We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.Thank you.
