How to add missing dates in between records.

[Reddy5833]

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 the min and max dates of that particualr employee I want to show as zeros.

Please find the below screenshots.

Actual

Expected

In my fact table i have date as integer and I have date dimension to link it with.

Should I do it in dataset level or visual level ?

Can some one Please help me how I can achieve this. 

Thanks in advance

[Anonymous]

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]

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.