Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
dbollini
Helper II
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

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

then difference between orginalworkhours-Totalhours

dbollini_1-1692306981005.png

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
amitchandak
Super User
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'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY),
'Table'[OriginalWorkHours] - 'Table'[Total Hours],
BLANK()
)

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

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

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

View solution in original post

5 REPLIES 5
dbollini
Helper II
Helper II

amitchandak
Super User
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'[ApprovalDateTime] > DATEADD(OneMonthAgo, -1, DAY),
'Table'[OriginalWorkHours] - 'Table'[Total Hours],
BLANK()
)

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
 

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

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

Thank you and i tried

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!