Forum Discussion
Using a Table with Multiple Date Fields
Hello,
I have a table that has mutiple date fields:
Project Table:
| requested completion date | initiation date | completion date | delivery date |
along with several other columns relating to the projects listed
And I would like to use these all in separate visuals, however have a single slicer that will act on all of them. My thinking would be to make many-to-one relationships between each of these date columns and a single date column of my Calendar table.
Calendar table:
| date (MM/DD/YY) |
that has many other columns further breaking down date (Month #, Day Name, Day of Week, etc.)
The only issue with this is, when creating visuals, I cannot get date heirarchies for those fields. I would need to use the Date column from the Calendar table in my visual, but because all of those Project date columns have a relationship with this one Calendar date column it would lead to an issue because there wouldn't be a way to specify which relationship (and which date) I want to use in that particular visual.
Ex.
graph over time
x-axis: Completion dates
y-axis: volume of projects completed
Has anyone thought of a good workaround, a way to display a variety of visuals from a table that has multiple date fields while still being able to use a single slicer for all dates?
5 Replies
Ah, the good-old multiple dates problem 🙂
Depending on your use case (what do you want to achieve) there might be variety of possible solutions (each and every requires measures to write):
1. Creating Multiple Inactive Relationships and specify relation (USERELATION) for each measure => e.g:Volume of projects (BY DATE COMPLETED) = Calculate(COUNT(Projects[Id],USERELATIONSHIP(Calendar[Date],Projects[CompletedDate])
2. Do not use relationship at all but use filter in each measure:
Volume of projects (BY DATE COMPLETED) = Calculate(COUNT(Projects[Id], CompletedDate <= Max(Calendar[Date]) && CompletedDate >= Min(Calendar[Date])
The second approach is more flexible allowing for combining dates conditions.
...
There are more solutions, each depending on your use case. Can you paste the mockup of the report and explanation of how slicer should work?
Hey jeremic_nikola thanks for the response, here are the tables I'm working with:
Project Table
Project Name Client Estimated Hours Working Hours Project Manager Requested Completion Date Initiation Date Completion Date Apple Apple1 15 21 Joe 10/1/2023 9/1/2023 10/5/2023 Orange Orange1 35 48 Jane 8/30/2023 7/15/2023 9/15/2023 Banana Banana1 5 4 Bob 11/1/2023 8/15/2023 10/25/2023 Here is a snippet of my calendar table
Date Year Month Name MMM Month Day Name DDD Day of Week YYMM MonthID Working Day Start End 1/1/2021 2021 January Jan 1 Friday Fri 6 2101 373 1 1/1/21 8:00 AM 1/1/21 5:00 PM 1/4/2021 2021 January Jan 1 Monday Mon 2 2101 373 1 1/4/21 8:00 AM 1/4/21 5:00 PM 1/5/2021 2021 January Jan 1 Tuesday Tues 3 2101 373 1 1/5/21 8:00 AM 1/5/21 5:00 PM
I am intending for the slicer to work universally. It will be a between dates slicer, and all graphs in the canvas that use dates should be controlled by the two between dates of this single slicer.
I am hoping to do more than just volumn, and to work with data that relates to the project managers, clients, etc.
Ok and how your report should look like?
When you choose dates, shoud the filter behave like OR condition between any of the dates?What do you mean by universally?
