Power Apps Componet Holiday Calculation 使用文档

关于input 和 output, 请查看 https://github.com/TheMiao/PowerApps-Component/tree/master/Holiday%20Calculation

 

所有功能在OnReset中

DateDiff

varDateDiff,这是使用了DateDiff 来计算两个date之间有多少天. 

 

Set(
    varDateDiff,
    DateDiff(
        HolidayCalculation.StartDate,
        HolidayCalculation.EndDate,
        Days
    )
);

 

Date Diff Except Holiday

varDateDiffExceptHoliday计算出同时除去周末以及假期

Set(
    varDateDiffExceptHoliday,
    RoundDown(
        DateDiff(
            HolidayCalculation.StartDate,
            HolidayCalculation.EndDate,
            Days
        ) / 7,
        0
    ) * 5 + Mod(
        5 + Weekday(HolidayCalculation.EndDate) - Weekday(HolidayCalculation.StartDate),
        5
    ) - CountRows(
        Filter(
            HolidayCalculation.HolidayRecord,
            HolidayDate >= HolidayCalculation.StartDate,
            HolidayDate <= HolidayCalculation.EndDate
        )
    )
);

 

Date Diff Except Weekend

计算出除去周末以外的天数

Set(
    varDateDiffExceptWeekend,
    RoundDown(
        DateDiff(
            HolidayCalculation.StartDate,
            HolidayCalculation.EndDate,
            Days
        ) / 7,
        0
    ) * 5 + Mod(
        5 + Weekday(HolidayCalculation.EndDate) - Weekday(HolidayCalculation.StartDate),
        5
    ));

 

TargetDateExceptHoliday

计算出除去周末以及holiday以外的最终目标天数

Clear(varDateRange);
ForAll(
    HolidayCalculation.DatePeriod,
    Collect(
        varDateRange,
        {
            RowIndex: CountRows(varDateRange) + 1,
            Date: DateAdd(
                HolidayCalculation.StartDate,
                Value
            )
        }
    )
);

RemoveIf(
    varDateRange,
    Weekday(Date) = 1 || Weekday(Date) = 7 || CountRows(
        Filter(
            HolidayCalculation.HolidayRecord,
            HolidayDate >= Date, 
            HolidayDate <= Date
        )
    ) > 0
);

Set(varTargetDateExceptHoliday, Last(FirstN(varDateRange, HolidayCalculation.NumberOfDays)).Date);

 

DatePeriod

Table类型, 需要输入一组数据来计算数据最大数以内的这些天数holiday以及weekend.

e.g.  这里只有20天, 所以只能计算出20天的holiday以及weekend.

[
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11,
        12,
        13,
        14,
        15,
        16,
        17,
        18,
        19,
        20,
    ]

 

Holiday Record

Table类型, 输入假日时间

Table(
    {
        Title: "National Day",
        HolidayDate: Date(
            2020,
            10,
            01
        )
    },
    {
        Title: "Labour Day",
        HolidayDate: Date(
            2020,
            05,
            01
        )
    }
)

 

StartDate

输入开始时期

 

EndDate

输入结束日期

 

Number Of Days

number类型, 输入需要计算的天数. 用于计算 Target Date