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.
I am trying to figure out an issue with a formula that I have created. I am needing to modify this formula to add an extra day to the "DATE IN" field but ignore dates that have the same "DATE IN" and "DATE OUT". Here is the formula that I am currently using:
# of Days =
VAR DateOutBlank = IF(ISBLANK(MJS_PRICE_PW[DATE OUT]),0,MJS_PRICE_PW[DATE OUT])
RETURN CALCULATE(SUM('Date'[Workday Not Holiday]),FILTER(MJS_PRICE_PW,IF(MJS_PRICE_PW[DATE IN]=MJS_PRICE_PW[DATE OUT],1)),DATESBETWEEN('Date'[Date],MJS_PRICE_PW[DATE IN],DateOutBlank))
Solved! Go to Solution.
Hi @Matt_Mohawk ,
Please try to update the formula of your calculated column [# of days] as below:
# of Days =
VAR _days =
CALCULATE (
SUM ( 'Date'[Workday Not Holiday] ),
DATESBETWEEN ( 'Date'[Date], MJS_PRICE_PW[DATE IN], MJS_PRICE_PW[DATE OUT] )
)
RETURN
IF (
ISBLANK ( MJS_PRICE_PW[DATE OUT] ),
BLANK (),
IF (
MJS_PRICE_PW[DATE IN] = MJS_PRICE_PW[DATE OUT],
1,
_days
- IF ( NOT ( WEEKDAY ( MJS_PRICE_PW[DATE IN], 1 ) IN { 1, 7 } ), 1, 0 )
)
)
Best Regards
@amitchandak thank you for that formula. I am getting the error, "The start date in Calendar function can not be later than the end date."
I am working with some projects that do not have a "DATE OUT" entered yet. Could this be the cause of that problem? That is the main reason why I had the VAR DATEOUTBLANK in my formula before.
@Matt_Mohawk , add an if on top of it and check if it works. or share small sample with expected output
Below is an example of what I am needing. The areas marked in red show that right now it is counting as 2 days, this is actually supposed to be 1 day because we do not count the day that it comes into us. The green area is the area that I want the formula to ignore because if a project comes in and goes out on the same day, it needs to be counted as 1 day. Hope this makes sense.
Hi @Matt_Mohawk ,
Please try to update the formula of your calculated column [# of days] as below:
# of Days =
VAR _days =
CALCULATE (
SUM ( 'Date'[Workday Not Holiday] ),
DATESBETWEEN ( 'Date'[Date], MJS_PRICE_PW[DATE IN], MJS_PRICE_PW[DATE OUT] )
)
RETURN
IF (
ISBLANK ( MJS_PRICE_PW[DATE OUT] ),
BLANK (),
IF (
MJS_PRICE_PW[DATE IN] = MJS_PRICE_PW[DATE OUT],
1,
_days
- IF ( NOT ( WEEKDAY ( MJS_PRICE_PW[DATE IN], 1 ) IN { 1, 7 } ), 1, 0 )
)
)
Best Regards
@v-yiruan-msft That did it! You are a lifesaver! Now I only have to figure out how you got it to work so I can learn to do this for later jobs.
@Matt_Mohawk , You can working days like
example measure
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min(Table[Start Date]),Max(Table[End Date])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
example column
Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Start Date],Table[End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
Also check 2nd page for workday date diff attcahed file using date table
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |