cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Reddy5833
Frequent Visitor

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

Please find the below screenshots.

Reddy5833_0-1656519138453.png

 

Actual

Reddy5833_3-1656519419655.png

Expected

Reddy5833_1-1656570555050.png

 

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

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1656924001747.png

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

vyangliumsft_1-1656924001749.png

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

vyangliumsft_2-1656924001756.png

Result:

vyangliumsft_3-1656924001760.png

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:

vyangliumsft_4-1656924001763.png

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

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.