Forum Discussion

aashton's avatar
aashton
Icon for Helper V rankHelper V
1 year 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 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???
Need Help

10 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      aashton
      Icon for Helper V rankHelper V
      1 year ago

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

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    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] ) )
)
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      aashton
      Icon for Helper V rankHelper V
      1 year ago

      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]
      )
  • aashton's avatar
    aashton
    Icon for Helper V rankHelper V
    1 year ago

    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
    )
  • aashton's avatar
    aashton
    Icon for Helper V rankHelper V
    1 year 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
    )

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    1 year ago

    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.