Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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- 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
@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
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.
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?
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!
smopure, you are amazing!! thank you!!
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |