Diving two measure with different date column giving wrong output

[ankurbajaj07]

I am stuck in a problem. I receive two excel files from my HR every month end. One is monthly (for that month) Sepration list  & second is cumulative Employee Roster.

I need a report table with side by side data clumn of 3 months.

Table is dvision of two measures.

Measure 1: count of rows in Excel table "Seperation list" where seperation is due to "RESIGNED" taking from column "SEPERATION DESCRIPTION". Table has a date column (created in query)referring to the date (month end) when the file was received from HR.

Mesure 2: count of rows in Excel Table "Employee Roster" where employee type is REGULAR employee. Table has a date column (created in query)referring to the date (month end) when the file was received from HR.

Output required is : Measure 1 / Measure 2 which is Attririon rate

My problem is both measure 1 & 2 refer different date column in different excel table. So visual table created based on division of two measures does not work properly, as if I drop date column from Seperation list, measure 1 works properly but measure 2 behave irratically giving wrong diviosn result. Similar is the case if i drop date column of Employee roster for visual table, measure 1 behaves incorrectly and output result is not correct.

PLease help.

[krishb1414 · Accepted solution]

Hi @ankurbajaj07  create one dimension date table with date columns from both the sheets and create relationship and use date column from date dimension in the 2 measures. 

for combine both date columns use UNION.

Thanks

[krishb1414 · Accepted solution]

Hi @ankurbajaj07  create one dimension date table with date columns from both the sheets and create relationship and use date column from date dimension in the 2 measures. 

for combine both date columns use UNION.

Thanks

[ankurbajaj07 · Accepted solution]

Hi,

Thanks for the rprly. Can you elaborate on create data table . Are you suggesting to do with power query ? Actually can u guide me , I am pretty new to power bi. 

[ankurbajaj07]

Hi, Thanks. I figured out the steps. thanks that worked.

[krishb1414]

That's great, You can ask me if anything required.