Forum Discussion

dbollini's avatar
dbollini
Icon for Helper II rankHelper II
3 years ago

Calculate the difference between two hour columns and compare it to last month date

Hello,

 

 

I want to calculate the hours difference between Total Hours has decimal values 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

 

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

 

 

 
"CALCULATE"
"DAX"

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi dbollini 

    If you want Total hour to be unaffected by slicer, you can modify your Test Measure Hours as follows.

    Test Measure Hours =
VAR a =
    CALCULATE (
        SUM ( 'ATAS REPORT'[Total Hours] ),
        ALLEXCEPT ( 'ATAS REPORT', 'ATAS REPORT'[Name] )
    )
RETURN
    a - SUM ( 'ATAS REPORT'[OriginalWorkHours] )

    If the following above cannot meet your requirement, can you provide more detail expression?

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • dbollini's avatar
      dbollini
      Icon for Helper II rankHelper II
      3 years ago

      Thank you but now the requirements have been modified first i have to check if the below conditions have been met and then should calculate the difference between orginalwkhours and Total hours

       

      isnull(TAE.OriginalWorkHours,0) != TAE.WkHours

      TAE.OriginalWorkHours is not null

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