Forum Discussion

dbollini's avatar
dbollini
Helper II
3 years ago
Solved

Calculate the difference between two hour columns numeric values after certain conditions

I want to calculate the hours difference between Total Hours and orginal work hours which has date slicer . Dax Query should be written as below and after this conditions have been met then have to ...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    dbollini , check if this can help

    CalculatedColumn =
    VAR MaxDate = TODAY()
    VAR OneMonthAgo = EOMONTH(MaxDate, -1)

    RETURN
    IF(
    'Table'[Active] = 1 &&
    'Table'[ApprovalStatusID] IN {5, 7, 8} &&
    'Table'[OriginalWorkHours] <> 'Table'[Total Hours] &&
    NOT(ISBLANK('Table'[OriginalWorkHours])) &&
    'Table'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY),
    'Table'[OriginalWorkHours] - 'Table'[Total Hours],
    BLANK()
    )

     

     

  • sergej_og's avatar
    sergej_og
    3 years ago

    what happens when you adjust 'Table'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY) to

    'Table'[ApprovalDateTime] > DATEADD('Calendar'[Date], -1, DAY)?

amitchandak's avatar
amitchandak
Super User
3 years ago

dbollini , check if this can help

CalculatedColumn =
VAR MaxDate = TODAY()
VAR OneMonthAgo = EOMONTH(MaxDate, -1)

RETURN
IF(
'Table'[Active] = 1 &&
'Table'[ApprovalStatusID] IN {5, 7, 8} &&
'Table'[OriginalWorkHours] <> 'Table'[Total Hours] &&
NOT(ISBLANK('Table'[OriginalWorkHours])) &&
'Table'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY),
'Table'[OriginalWorkHours] - 'Table'[Total Hours],
BLANK()
)

 

 

  • dbollini's avatar
    dbollini
    Helper II
    3 years ago

    Thank You for Taking time to answer this and i tried various ways

    'Table'[ProjectApprovalDate] >  DATEADD(PREVIOUSMONTH(Dates[Month]),
     
    If i do this it says :-The last argument must be one of these keywords: DAY, MONTH, QUARTER, or YEAR
     
    and
    if i do this for previous month it says
    'Table'[ProjectApprovalDate] >  DATEADD(PREVIOUSMONTH(Dates[Month],-1,day),
    Too many arguments were passed to the PREVIOUSMONTH function. The maximum argument count for the function is 1.
    and if i try what you put in there 'Table'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY),
    then it says incorrect syntax
    Can you please check once thanks
     
    • sergej_og's avatar
      sergej_og
      Super User
      3 years ago

      what happens when you adjust 'Table'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY) to

      'Table'[ApprovalDateTime] > DATEADD('Calendar'[Date], -1, DAY)?

      • dbollini's avatar
        dbollini
        Helper II
        3 years ago

        Thank you and i tried

        DATEADD('Dates'[Date],-1, DAY) yours and it worked