Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |