Forum Discussion

Reddy5833's avatar
Reddy5833
Helper II
4 years ago

How to add missing dates in between records.

Hi,  I have employee time sheet data, there are some missing dates for few employees I just want to show them as zeros in the table visual in power bi,  If thare are any missing dates in  between t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  Reddy5833 ,

Here are the steps you can follow：

1. Go to Power query, select Merge Queries - [Date] of Date table and [Date] of Table - Join Kind - Left outer.

2. Select the yellow mark of Table in the new table that comes out.

3. Select the three columns – Transform – Fill – Down.

Result：

4. Create calculated column.

Works =
var _column=SELECTCOLUMNS('Table',"1",[Date])
return
IF(
    'Merge2'[Date] in _column,
    CALCULATE(SUM('Table'[Working hours]),FILTER(ALL('Table'),'Table'[Date]='Merge2'[Date])),0)
Breaks =
var _column=SELECTCOLUMNS('Table',"1",[Date])
return
IF(
    'Merge2'[Date] in _column,
    CALCULATE(SUM('Table'[Break Hours]),FILTER(ALL('Table'),'Table'[Date]='Merge2'[Date])),0)

5. Result:

PBIX files can be downloaded here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Reddy5833's avatar
Reddy5833
Helper II
4 years ago

Hi Yangliu,

 

Thanks for the reply and your effort, I'm not able to open the PBIX file you shared.

Does this solution work in below scenario.

 

Scenario: I have time sheet data fact table for few employees for the month of January and February.

Example-

Employee A: has no missing dates in the whole Jan month

Employee B: has few missing dates in Jan month

Employee C: has started working from February so he dont have Jan data

 

When I merge this fact table with date dim to fill the missing dates

Q1) Does it fill the missing dates in January for Employee B, or does it ignore filling because Employee A has all January dates.

Q2) Does it fill the Janury dates for Employee C (Which is not expected), Since the employee joined in the month of February it should fill missing dates between his min and max dates of time sheet.