Forum Discussion
How to deal with multiple date columns
Hellos,
I wanted to ask you about a unique issue I am facing.
I work in IT and am creating a dashboard for our team. We have date columns for created, closed, updated tickets. I have created a DAX DateTable making CreatedDate as the active relationship and ClosedDate & UpdatedDate as inactive relationships as most of the report charts are based on created date. Then I created measures for closed, last updated tickets using USERELATIONSHIP function so that they can be put together in a time-based line chart.
I now have a requirement to show average closed time based on ClosedDate on the x-axis. However if I add the Year-Month, Week number, DayOfTheWeek,etc column from my DateTable, it picks the CreatedDate as the default for the axis. How do I get over this issue?
Anonymous , Create a measure with use relationship first
M1 = calculate(Count(Table[Ticket]), USERELATIONSHIP ('DateTable'[Date], Table[Closed Date]) )
Now use M1 to do all calculations. Or always have this calculation first
example
AverageX(Values('DateTable'[Date]), calculate(Count(Table[Ticket]), USERELATIONSHIP ('DateTable'[Date], Table[Closed Date]) ) )
3 Replies
- amitchandak
Super User
Anonymous , Create a measure with use relationship first
M1 = calculate(Count(Table[Ticket]), USERELATIONSHIP ('DateTable'[Date], Table[Closed Date]) )
Now use M1 to do all calculations. Or always have this calculation first
example
AverageX(Values('DateTable'[Date]), calculate(Count(Table[Ticket]), USERELATIONSHIP ('DateTable'[Date], Table[Closed Date]) ) )
You will start by having 3 base measure which you will then use to calculate also the time inteligence variations:
- Created Tickets - Will be a simple count, as it will use the active relationships.- Updated Tickets - Will be a Calculate measure like Amit wrote above. This one with the closed field.
- Closed Tickets - Will be a Calculate measure like Amit wrote above. This one with the updated filed.
Take into account all date fields, will need to be a date and not a datetime. Wait 2-3 days and look at this channel: https://www.youtube.com/c/SSBICentral.
There will be a video uploaded with something very similar towards the end of the user group sessions, to what you are trying to achieve.
Thanks amitchandak Anonymous
However I have a calculated columns which gives "DaysToClose", which is (ClosedDate-CreatedDate).
When I try to find the average days to close and plot in on a chart, it gives me as per the "Created Date" because the Date Table has an active relationship with the "Created Date". But my boss wants to see the average close time plotted as per the "Closed Date". I am going crazy. lol.