Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two dates in a table, Registering Day and Day out.
There will always be a registering date, but not always a day out. I need to work out the average daby between but this is casuing errors beciase one of the days will be blank. I have started with something like this but can anyone guide me on how to correct it. Thanks
Workingdays = CALCULATE(SUM('DateKey'[IsWorkingDay]), DATESBETWEEN(DateKey[Date],'Put Away Headers'[Registering_Date],'Put Away Headers'[Day_Out])) -- Here i need to filter out blank "Day Out"
Solved! Go to Solution.
Hi @LewisH,
If I understand you correctly, the formula below should work in your scenario.
Workingdays = IF ( ISBLANK ( 'Put Away Headers'[Day_Out] ), BLANK (), CALCULATE ( SUM ( 'DateKey'[IsWorkingDay] ), DATESBETWEEN ( DateKey[Date], 'Put Away Headers'[Registering_Date], 'Put Away Headers'[Day_Out] ) ) )
Regards
Hi,
Does this work?
=CALCULATE(SUM('DateKey'[IsWorkingDay]),FILTER('Put Away Headers','Put Away Headers'[Day_out]<>BLANK()),DATESBETWEEN(DateKey[Date],'Put Away Headers'[Registering_Date],'Put Away Headers'[Day_Out]))
Hi,
Does this work?
=CALCULATE(SUM('DateKey'[IsWorkingDay]),FILTER('Put Away Headers','Put Away Headers'[Day_out]<>BLANK()),DATESBETWEEN(DateKey[Date],'Put Away Headers'[Registering_Date],'Put Away Headers'[Day_Out]))
Hi @LewisH,
If I understand you correctly, the formula below should work in your scenario.
Workingdays = IF ( ISBLANK ( 'Put Away Headers'[Day_Out] ), BLANK (), CALCULATE ( SUM ( 'DateKey'[IsWorkingDay] ), DATESBETWEEN ( DateKey[Date], 'Put Away Headers'[Registering_Date], 'Put Away Headers'[Day_Out] ) ) )
Regards
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |