Forum Discussion
Compare dates in a summarised table
Hi Experts
I have summarise data based on columns from a customer and a training outcome table, Im gtrying to find the number of days from the customer start date to the date they found a job. My dax works well to summarise however I now am finding it difficult to compare the dates, the error is the date holders (start date, outcome date) cannot be found. I have tried to use variables but still no luck, so wondering where I am going wrong. Here is my DAX:
Time_to_employment =
SUMMARIZECOLUMNS(
'Customer'[Customer ID], 'Customer'[Start Date],'Training_Outcome'[Contract Type],
"Job Outcome",
CALCULATE(MAX('Training_Outcome'[Outcome Date]),
FILTER( 'Training_Outcome',
'Training_Outcome'[Customer ID] = SELECTEDVALUE('Training_Outcome'[Customer ID])
&& 'Training_Outcome'[Type] = "Employed"),
"Start to employment", Networkdays(Start_Date, Outcome_Date)- 1
))
All help appreciated, thank you
Hi, Elisa112
Is this the result you expected? If not, please provide more information.
Days to employment = DATEDIFF([Start Date],[Outcome Date],DAY)Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
You could create a measure like
Start to employment = VAR CustomerID = SELECTEDVALUE ( 'Customer'[Customer ID] ) VAR StartDate = SELECTEDVALUE ( 'Customer'[Start Date] ) VAR EndDate = CALCULATE ( MAX ( 'Training_Outcome'[Outcome Date] ), TREATAS ( { ( CustomerID, "Employed" ) }, 'Training_Outcome'[Customer ID], 'Training_Outcome'[Type] ) ) VAR Result = NETWORKDAYS ( StartDate, EndDate ) - 1 RETURN ResultHi, Elisa112
Have you solved your problem? If not, can you provide example data and your desired output?
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous
My summary table looks like this
Customer ID Start Date Outcome Date OutcomeType
123 15 Jan 2024 15 March 2024 Employed
I looking for the following output:
Customer ID Start Date Outcome Date OutcomeType Days to employment
123 15 Jan 2024 15 March 2024 Employed 90
I would rather the column days to employment is a calculated column rather than a measure
Thanks for your assistance
Hi, Elisa112
Is this the result you expected? If not, please provide more information.
Days to employment = DATEDIFF([Start Date],[Outcome Date],DAY)Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
