Compare dates in a summarised table

[Elisa112]

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

[Anonymous]

Hi, @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.

[Elisa112]

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

[Anonymous · Accepted solution]

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.

[Elisa112]

Thank you

[johnt75]

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
    Result

[Elisa112]

@johnt75 

thank you