can't find the missing dates from Date table those the users not enters the time sheet dates for day

[VidSagar]

Hi,
 
I have WorkLog table, in the table 'WorkLog Date' column contains the employees daily entered time sheet information as how much time they spent for issue by filling hours daily.. In my case, i have to find if an employee missed to enter the time sheet for the day, need to show the specific day for Missing day of filling time sheet. (Not Weekends, Working Day). I have created a LookUp table as DATE table from the period of 2016 to 2020 and applied the filter on the DATE table by Year wise and Month wise, and i have created a relationship for WorkLog table to DATE table, and wrote a calculation like as,
 
Missing Dates =
if(if(ISBLANK(LOOKUPVALUE(WorkLogs[Dated],
WorkLogs[Date],'DATEKey'[Date]))=true,
if(DATEKey[Date]>TODAY(),0,DATEKey[Date]),0)<Min(WorkLogs[Dated]),0,DATEKey[Date])
 
and
 
DAY = SWITCH(WEEKDAY(DATEKey[Missing Dates],2),1,"Monday",2,"Tuesday",3,"Wednesday",4,"Thrusday",5,"Friday",6,"Saturday",7,"Sunday")
but its not giving expected results,
 
Could someone guide me how to solve this issue..
 
Thanks.............

[vanessafvg · Accepted solution]

@VidSagar can you post screenshots of the data with the relationship view?

[v-shex-msft · Accepted solution]

Hi @VidSagar,

First of all, I want to confirm that you wish to find the missing work date in your fact table, right?

If this is a case, perhaps you can take a look at below steps:

1. Filter on calendar table to get the work date(Monday to Friday).

2. Get users list from fact table and use 'CROSSJOIN' function to create the all users work date table.

3. Use 'EXCEPT' function to remove fact table records form all users general work date table, then you will get the not match work date.

I think if you share the sample file it will be help for write the detail formulas.

Regards,

Xiaoxin Sheng

[VidSagar · Accepted solution]

Hi XiaoXin, thank you for your suggestion, 

here i am facing the issue as below...

I have approached & tried multiple ways, Whenever if i am selecting an employee name in my report as per above,

Here, its not displaying dates for Employees working date missing time sheet days. I.E. for above employee has filled last time sheet on 7/17/2017, after that 7/18/2017 and 7/19/2017 he missed to fill the time sheets for these two days, whenever if i am seleting the employee name by applying the filter for Year 2017, JULY Month its not showing the Missing dates of Working days "7/18/2017 and 7/19/2017" in working day slicer, its displays as blank and for Weekend slicer displays "Saturday & Sunday". Not Displaying Missing Working Day Time Sheets "7/18/2017" & "7/19/2017"

Could you plz help on this....

Thanks...

[vanessafvg · Accepted solution]

@VidSagar can you post screenshots of the data with the relationship view?

[VidSagar]

[v-shex-msft · Accepted solution]

Hi @VidSagar,

First of all, I want to confirm that you wish to find the missing work date in your fact table, right?

If this is a case, perhaps you can take a look at below steps:

1. Filter on calendar table to get the work date(Monday to Friday).

2. Get users list from fact table and use 'CROSSJOIN' function to create the all users work date table.

3. Use 'EXCEPT' function to remove fact table records form all users general work date table, then you will get the not match work date.

I think if you share the sample file it will be help for write the detail formulas.

Regards,

Xiaoxin Sheng

[VidSagar · Accepted solution]

Hi XiaoXin, thank you for your suggestion, 

here i am facing the issue as below...

I have approached & tried multiple ways, Whenever if i am selecting an employee name in my report as per above,

Here, its not displaying dates for Employees working date missing time sheet days. I.E. for above employee has filled last time sheet on 7/17/2017, after that 7/18/2017 and 7/19/2017 he missed to fill the time sheets for these two days, whenever if i am seleting the employee name by applying the filter for Year 2017, JULY Month its not showing the Missing dates of Working days "7/18/2017 and 7/19/2017" in working day slicer, its displays as blank and for Weekend slicer displays "Saturday & Sunday". Not Displaying Missing Working Day Time Sheets "7/18/2017" & "7/19/2017"

Could you plz help on this....

Thanks...

[v-shex-msft · Accepted solution]

Hi @VidSagar,

Can you please share us sample pbix file to test?

Regards,

Xiaoxin Sheng