how to write a DAX formula that is equivalent to an excel formula?

[Anonymous]

Hi community, the first two columns are dates and the circled column is the final result which shows the difference in days between the two dates EXCLUDING weekends.  The formula that I used is below.  If I wanted to do the same thing inside PowerBI (adding a new measure), how would I use this same formula using the DAX language?  Or, will PowerBI accept the excel formula?  Thank you!

[Greg_Deckler · Accepted solution]

Here's the other one:

Column 2 = 
VAR Calendar1 = CALENDAR([created date],[review date])
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " & HOUR(MOD([review date]-[created date],1)) & " Hours " & MINUTE(MOD([review date]-[created date],1)) & " Minutes"

Same thing with the measures.

[Greg_Deckler · Accepted solution]

@Anonymous- I created a Quick Measure for this and submitted it to the Gallery:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[Greg_Deckler · Accepted solution]

Here's the other one:

Column 2 = 
VAR Calendar1 = CALENDAR([created date],[review date])
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date]) & " Days " & HOUR(MOD([review date]-[created date],1)) & " Hours " & MINUTE(MOD([review date]-[created date],1)) & " Minutes"

Same thing with the measures.

[Anonymous]

smoupre,

Okay, I feel a bit dumb, looks like the reason I'm getting that error is because there are some cells that contain a blank.  This would indicate that the file has not yet been reviewed.  In cases like this, how can this formula be amended to state something like "not yet reviewed" if a cell in the column "Field Assigned Time Stamp" is blank?

[Anonymous]

Hi smoupre,

I tried using your DAX formula in a different data set and I received an error message stating...

"The start date or end date in Calendar function can not be Blank value."

I made sure to update the column names to reflect the new data set and the cell is formatted exactly as my original.

mm/dd/yyyy hh:mm

Please help!

[Anonymous]

smopure, you are amazing!!  thank you!!

[Greg_Deckler]

OK, so for the first column it will be close to that formula:

Column = DATEDIFF([created date],[review date],DAY) & " Days " & HOUR(MOD([review date] - [created date],1)) & " Hours " & MINUTE(MOD([review date] - [created date],1)) & " Minutes"

To make it a measure, just wrap your columns in an aggregation MAX or something. Working on getting rid of weekends, that tends to be a littel tricky.