Using a Table with Multiple Date Fields

[ChrisR22]

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?

[jeremic_nikola]

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?

[ChrisR22]

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. 

[jeremic_nikola]

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?

[ChrisR22]

- could you clarify what you mean for this first question?

- i'm intending to have a slicer where I select any two dates and all charts will only represent data between those two dates. for example a chart showing volume of projects based on completion date over time would show only between dates A and B in the slicer, and an ajacent chart showing volumn of projects based on initiation date over time would also onnly show between dates A and B.

[jeremic_nikola]

Oh that - this is easy - just apply solution 1 (by specifying USERELATIONSHIP in measures) and you will get it:
CHART 1 - This Measure: Volume of projects (BY DATE COMPLETED) = Calculate(COUNT(Projects[Id],USERELATIONSHIP(Calendar[Date],Projects[CompletedDate])
CHART 2 - This Measure: Volume of projects (BY DATE INITIATED) = Calculate(COUNT(Projects[Id],USERELATIONSHIP(Calendar[Date],Projects[InitiatedDate])
....

So, to summarize:

1. Create INACTIVE Relationships between Calendar Date and each date column you want to do breakdowns

2. Create MEASURES for each chart you want (by completed date, initiation date etc...)
3. Put respective measures in respective charts

4. Put ONE Slicer with Calendar Date
5. Enjoy....