Exclude dates from calculation that aren't present in another dataset

[Danielnir]

Hi everyone!

I made a report that shows recorded work versus staff clockings. The first dataset is updated daily and the second one is weekly. How to exclude those days where I have recorder work but do not have staff clockings yet? 

Picture explanation:

The date picker comes from the date dimension connected to all other datasets. 

Recorded on Eff Sheets, Other labour and Clockings are stored in disparate datasets. 

The first two are being updated daily and the last one, weekly.

Code that I tried to implement but didn't work:

Time Available = 
var Eff_Time = sum('Eff. Log'[Manifest time])
var OLJ_Time = sum('Other Labour Events'[Total Time (h)])
var Total_Recorded = Eff_Time+OLJ_Time

var Astrow = calculate(sum('Astrow Report'[Break Time]),filter('DIM: Date', NOT('Astrow Report'[Date] IN VALUES('DIM: Date'[Date Picker]))))+0

return

divide(Total_Recorded,Astrow)

May I ask for your assistance?

[Anonymous]

HI @Danielnir,

You can extract the table date and calendar table date values and use except function to extract not include parts and store them into a variable, then you can use this variable as a condition to exclude calculation date ranges.

VAR list = EXCEPT ( ALL ( 'DIM: Date'[Date Picker] ), ALL ( 'Table'[Date] ) )

Regards,

Xiaoxin Sheng

[amitchandak]

@Danielnir , Is DIM: Date is an independent table in this case?

for not in is should not have join or should have inactive join

[Danielnir]

@amitchandakthanks for your reply! 

"DIM: Date" is connected to all the others and is filtering them. 

Hope it helps.