使用PowerBI在DATEDIFF度量中排除周末和公共假日

Question

我试图排除以下PowerBi度量的周末和公共假日，

First Report = IF(abs(DATEDIFF(C[CreatedDate].[Date],C[Earliest Date max per ID],DAY)+1)<=2,"OnTime","Late")

基本上，该措施应该检查这些日期之间的差异是否等于或小于一天，不包括周末和公共假日。我不知道如何修改这项措施，因为它不包括周末和公共假日。我试过创建日历表，但不知道如何从那里开始。如果有人能帮我解决问题，我将不胜感激

Answer 1

创建一个表，将其命名为Public_Holiday&手动或从excel或任何其他来源输入公共假日日期。在查询编辑器中创建一个空白查询，并在预先编辑器中粘贴此M代码。我假设Sat&周日除外。

(StartDate as date, EndDate as date) as number =>
let
List.Dates = List.Dates(StartDate, Number.From(EndDate-StartDate)+1, #duration(1,0,0,0)),
RemoveWeekends = List.Select(List.Dates, each Date.DayOfWeek(_,Day.Monday)<5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, Public_Holiday),
CountDays = List.Count(RemoveHolidays)
in
CountDays

当您需要在查询编辑器中获取工作日时，可以随时调用此函数。

Answer 2

你可以试试这个

VAR allDates= DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
-- VAR allDates = GENERATESERIES ( StartDate, EndDate , 1 ) -- If you don't have a Calendar table 
-- and you need to check all dates in period.
-- It can create another column name for Filter()
VAR allDatesExceptHolidays = EXCEPT(allDates,VALUES('Holydays'[Date]))
VAR allDatesExceptHolidaysNoWeekend=
FILTER(
allDatesExceptHolidays 
,WEEKDAY([Date],2)<6
)
VAR DaysQty = COUNTROWS(allDatesExceptHolidaysNoWeekend)
RETURN 
DaysQty