Relate two date columns in a single table?

[Anonymous]

Hello,

I'm working in a table that has two date columns: DateSubmitted (for when a potential hire enters our recruitment system) and StartDate (the date on which a new hire begins work). 

My goal is to create a Slicer that defines the date range of the KPIs i'm tracking, while taking both of the above dates into account. Essentially, when I set a period in the Slicer, I'd like to show one KPI that would display how many potential hires entered our recruitment system during that period (i.e. count of DateSubmitted) and a second KPI that would display how many new hires started work during that same period (i.e. count of StartDate). 

I'm at a loss for how to do this. Do I need to combine DateSubmitted and StartDate into a single column?

This is my first week in Power BI, so I could really use some guidance. Your help is greatly appreciated! 

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>Do I need to combine DateSubmitted and StartDate into a single column?

Use unpivot columns feature to convert two date column to one is one of available methods.(you need to setting a condition to control filter works on which records)

Unpivot Data Using Excel Power Query

In addition, you can also create two relationship to calendar, then write two measures with userelationship function to choose which relations works for measure.

USERELATIONSHIP Function (DAX)

USERELATIONSHIP in Calculated Columns

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft, Xiaoxin, thank you! This worked great.