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.
Hello,
I am stuck on a formula that I just can't seem to wrap my head around. I am attempting to build a visual that simply shows the estimated completion date based on current averages.
I currently have a measure that tells me it's going to take "n" working days to complete the project at the rate we are averaging.
What I'm attempting to do now is create a measure that returns this.
Today + The above measure (only counting weekdays) = what date?
Here are my current date table and formal that isn't really getting me the results I want.
Date Year Month Number Month Year Month Number Week Number Numeric Day of Week Month Year IsWorkingDay Binary Weekday
Date | Year | Month Number | Month | Year Month Number | Week Number | Numeric Day of Week | Month Year | IsWorkingDay | Binary Weekday |
1/1/2020 | 2020 | 1 | January | 24240 | 1 | 3 | 20-Jan | TRUE | |
1/2/2020 | 2020 | 1 | January | 24240 | 1 | 4 | 20-Jan | TRUE | |
1/3/2020 | 2020 | 1 | January | 24240 | 1 | 5 | 20-Jan | TRUE | |
1/4/2020 | 2020 | 1 | January | 24240 | 1 | 6 | 20-Jan | FALSE | 1 |
1/5/2020 | 2020 | 1 | January | 24240 | 1 | 7 | 20-Jan | FALSE | 1 |
1/6/2020 | 2020 | 1 | January | 24240 | 2 | 1 | 20-Jan | TRUE | |
1/7/2020 | 2020 | 1 | January | 24240 | 2 | 2 | 20-Jan | TRUE | |
1/8/2020 | 2020 | 1 | January | 24240 | 2 | 3 | 20-Jan | TRUE | |
1/9/2020 | 2020 | 1 | January | 24240 | 2 | 4 | 20-Jan | TRUE | |
1/10/2020 | 2020 | 1 | January | 24240 | 2 | 5 | 20-Jan | TRUE | |
1/11/2020 | 2020 | 1 | January | 24240 | 2 | 6 | 20-Jan | FALSE | 1 |
1/12/2020 | 2020 | 1 | January | 24240 | 2 | 7 | 20-Jan | FALSE | 1 |
Solved! Go to Solution.
Hi @Almercie ,
try this
Estimated Finish Date with weekends =
VAR Today =
TODAY ()
VAR WorkingDaysThisWeek =
5 - WEEKDAY ( Today, 3 )
VAR WorkingDaysAfterThisWeek = [Estimated Working Days Till Completion] - WorkingDaysThisWeek
VAR DaysAfterThisWeek =
INT ( DIVIDE ( WorkingDaysAfterThisWeek, 5, 0 ) ) * 7
+ MOD ( WorkingDaysAfterThisWeek, 5 )
RETURN
IF (
[Estimated Working Days Till Completion] < WorkingDaysThisWeek,
Today + [Estimated Working Days Till Completion],
Today + WorkingDaysThisWeek + 2 + DaysAfterThisWeek
)
Hope this helps.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Almercie ,
Well this will work. Took me a while to figure it out, but I figured you can use RankX on a filtered datetable to get the desired output 🙂 I have used two tables, a datetable (DateTable) with column isWeekday (true or false) and the following test table (DaysToAdd):
The following measure gave me the desired result:
EndDateMeasure =
IF(HASONEVALUE(DaysToAdd[Index]),
VAR curDate = SELECTEDVALUE(DaysToAdd[StartDate])
VAR _daysToAdd = SELECTEDVALUE(DaysToAdd[DaysToAdd])
VAR _filteredDateTable = FILTER(DateTable, DateTable[Date] >= curDate && DateTable[isWeekday] = TRUE)
RETURN
MAXX(FILTER(
ADDCOLUMNS(_filteredDateTable, "@rank", RANKX(_filteredDateTable, [Date],, ASC)), [@rank] = _daysToAdd + 1),
[Date]))
Pulling the testtable and the measure in a Table visual will give you this:
Well this was a fun question to answer! Hope it helps, let me know if you have any questions!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @Almercie ,
try this
Estimated Finish Date with weekends =
VAR Today =
TODAY ()
VAR WorkingDaysThisWeek =
5 - WEEKDAY ( Today, 3 )
VAR WorkingDaysAfterThisWeek = [Estimated Working Days Till Completion] - WorkingDaysThisWeek
VAR DaysAfterThisWeek =
INT ( DIVIDE ( WorkingDaysAfterThisWeek, 5, 0 ) ) * 7
+ MOD ( WorkingDaysAfterThisWeek, 5 )
RETURN
IF (
[Estimated Working Days Till Completion] < WorkingDaysThisWeek,
Today + [Estimated Working Days Till Completion],
Today + WorkingDaysThisWeek + 2 + DaysAfterThisWeek
)
Hope this helps.
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |