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 calculate the difference between orginal workhours and Total hours

 

Conditions are below orginalwork hours should not be equal to total hours or cannot be null plus approval date time should be greater than previous month (1 month minus) than todays date

 

WHERE PROJ.Active = 1 AND TAR.ApprovalStatusID IN (5,7,8)

(TAE.OriginalWorkHours,0) != TAE.Total Hours

TAE.OriginalWorkHours is not null

TAR.ApprovalDateTime>DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, @pendwkdate)+1, 0))

then difference between orginalworkhours-Totalhours

I am trying to write the conditions in Dax but its saying its incorrect
= var = maxx(FILTER('ATAS REPORT','ATAS REPORT'[OriginalWorkHours] <> 'ATAS REPORT'[Total Hours] = 'ATAS REPORT'[Total Hours] return MAXX(FILTER('ATAS REPORT','ATAS REPORT'[OriginalWorkHours] <> NULL() )

 

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)?

5 Replies

  • 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)?