Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
2 years ago

DATEDIFF with SUM not working

In Power BI I've created a table with Pay Period Start Date, Pay Period End DAte, Check Date.  I have a calculated column to find the number of days in a pay period  ( I tried a million variations of...
Need Help
aashton's avatar
aashton
Icon for Helper V rankHelper V
2 years ago

I've tested all of these methods on a different table in my Power BI file, a spreadsheet that I am pulling in.  It all worked perfectly.  This table Pay Periods Bi-Weekly is a calculated table I created to create two week pay periods.  So it must have something to do with it being a calculated table??

Pay Periods Bi-weekly =
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(2025, 12, 31)
VAR PayPeriodStart = DATE(2024, 5, 5)
VAR PayPeriodLength = 14
VAR NumberOfDays = DATEDIFF(StartDate, EndDate, DAY)
RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "PayPeriodStart",
        VAR CurrentDate = [Date]
        RETURN PayPeriodStart + PayPeriodLength * INT((CurrentDate - PayPeriodStart) / PayPeriodLength),
    "PayPeriodEnd",
        VAR CurrentDate = [Date]
        RETURN PayPeriodStart + PayPeriodLength * INT((CurrentDate - PayPeriodStart) / PayPeriodLength) + PayPeriodLength - 1
)