cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## 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

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

2 ACCEPTED SOLUTIONS
Super User

@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'[OriginalWorkHours] - 'Table'[Total Hours],
BLANK()
)

Super User

5 REPLIES 5
Helper II
Super User

@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'[OriginalWorkHours] - 'Table'[Total Hours],
BLANK()
)

Helper II

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

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

Super User

Helper II

Thank you and i tried

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors