DATEDIFF with SUM not working

[aashton]

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 DATEDIFF and couldn't get it to work, so tried this):  

Days Pay Period = [PayPeriodEnd] - [PayPeriodStart] + 1

I need to find the total number of days where the Check Date was in the last month.  I have a Check Date Flag:  

Check Date Last Month = IF(DATEDIFF('Pay Periods Bi-weekly'[Check Date], TODAY(), MONTH)=1, 1, 0).   This works.

I've tried measures and calculated columns like:  

TotalDaysLastMonth =

SUMX(

    FILTER(

        'Pay Periods Bi-weekly',

        'Pay Periods Bi-weekly'[Check Date Last Month] = 1

    ),

    SELECTEDVALUE('Pay Periods Bi-weekly'[Days Pay Period])

) 

And it just keeps giving me very wrong anwers, off by hundreds.  I can't figure it out. ChatGPT can't figure it out.  Any ideas???

[parry2k]

@aashton  It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.

[aashton]

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

)

[parry2k]

@aashton send the pbix file and remove sensitive information before sharing.

[aashton]

The code to create the table is, don't know if that affects anything:   

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

)

[parry2k]

@aashton or try this:

TotalDaysLastMonth =
SUMX(
    SUMMARIZE ( 
      FILTER(
          'Pay Periods Bi-weekly',
          'Pay Periods Bi-weekly'[Check Date Last Month] = 1
       ),
       'Pay Periods Bi-weekly'[Pay Period Start Date],
       'Pay Periods Bi-weekly'[Pay Period End Date],
       'Pay Periods Bi-weekly'[Days Pay Period] )
    )
    [Days Pay Period]
)

[aashton]

Syntax error on this, missing a comma??

TotalDaysLastMonth =

SUMX(

    SUMMARIZE (

      FILTER(

          'Pay Periods Bi-weekly',

          'Pay Periods Bi-weekly'[Check Date Last Month] = 1

       ),

       'Pay Periods Bi-weekly'[PayPeriodStart],

       'Pay Periods Bi-weekly'[PayPeriodEnd],

       'Pay Periods Bi-weekly'[Days Pay Period] )

    )

    [Days Pay Period]

)

[parry2k]

@aashton not sure how the raw data looks like, try this:

TotalDaysLastMonth =
SUMX(
    FILTER(
        'Pay Periods Bi-weekly',
        'Pay Periods Bi-weekly'[Check Date Last Month] = 1
    ),
    CALCULATE ( MAX ( 'Pay Periods Bi-weekly'[Days Pay Period] ) )
) 

[aashton]

No, same incorrect answer.  

[parry2k]

@aashton try this:

TotalDaysLastMonth =
SUMX(
    FILTER(
        'Pay Periods Bi-weekly',
        'Pay Periods Bi-weekly'[Check Date Last Month] = 1
    ),
    CALCULATE ( SUM ( 'Pay Periods Bi-weekly'[Days Pay Period] ) )
) 

[aashton]

@parry2k  Thanks but I get the same incorrect answer. SEe below, it should be 42, and I get 588: