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