Real Working Days PART 2!

cms418 · October 13, 2021, 6:11pm

Hi everyone,

This post is a continuation of my another post “Real Working Days”. I know that this topic gets a lot of attention but I can’t seem to get the below correct or find an example on the forum.

@Harsh provided me a wonderful solution to Real Work Days in the attached. However, when I took this back to the end user they wanted yet another tweak and to only account for ACTIVE work days.

The ask from client;

Break down actual work days on site. NOT simply the days b/w MOB and DEMOB as Harsh helped me with in the ‘Real Work Days - Harsh’ calculated column.

See below for notables on the situation and notice that Mobilization and Access started and ended on same day (so should be 1 active day) and also that there is a gap where work b/w Excavation End Date and Demobilization Start Date where nobody was working.

I did attempt a measure (inside the date table) called ‘Actual Work Days 2’ with no luck.

There are more activities where this will apply in the model but for simplicity and demo purposes I just kept it to the above context of Mobilization, Access, Excavation and Demobilization - it seems I’ll be able to apply the pattern to the other activities similar to how I attempted the ‘Actual work days 2’ if figure it out.

Date Stuff3.pbix (3.7 MB)

Harsh · October 13, 2021, 6:55pm

Hello @cms418,

Thank You for posting your query onto the Forum.

Well, I actually didn’t understood the logic between the criteria’s - “Mobilization” and “Site Access”. Under the “Mobilization”, you’ve considered the day as 1 even though the start and end date is same whereas that same is happening with the “Site Access” as well and for that you’ve considered the day as 0. Why?

But anyways, to achieve the results the way you’ve specified into the screenshot you can write the measure as provided below -

Real Working Days - Harsh = 
VAR _Mobilization_Calculation =
DATEDIFF( 
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization Start Date] ) ,
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization End Date] ) ,
    DAY )

VAR _Access_Calculation = 
DATEDIFF( 
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access Start Date] ) ,
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access End Date] ) ,
    DAY )

VAR _Excavation_Calculation = 
DATEDIFF(
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation Start Date] ) ,
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation End Date] ) ,
    DAY )

VAR _Demobilization_Calculation = 
DATEDIFF(
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization Start Date] ) ,
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization End Date] ) ,
    DAY )

VAR _Results_1 = 
IF( _Mobilization_Calculation > 0 , 
    _Mobilization_Calculation , 
    1 )

VAR _Results_2 = 
IF( _Access_Calculation > 0 , 
    _Access_Calculation , 
    0 )

VAR _Results_3 = 
IF( _Excavation_Calculation > 0 ,
    _Excavation_Calculation , 
    0 )

VAR _Results_4 = 
IF( _Demobilization_Calculation > 0 , 
    _Demobilization_Calculation , 
    0 )

RETURN
_Results_1 + _Results_2 + _Results_3 + _Results_4

Here’s the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note: If you want to consider the day as “entire one single day” where start and end date is same then in that case under the “Results” variable just change the 0 to 1 and you’ll get the results accordingly.

Thanks and Warm Regards,
Harsh

Date Stuff3 - Harsh.pbix (3.7 MB)

cms418 · October 13, 2021, 7:05pm

Hey @Harsh ,

This was because I figured the result would be 1+1 = 2 days when in fact the workers were only on site for one day here yet accomplished 2 activities - make sense?

Also, does the below take out Sundays as the original column did? I’ll also layer holidays on after I build the holiday table into the model.

Harsh · October 13, 2021, 7:38pm

Hello @cms418,

Try the below provided measure that takes Sunday’s into the account and when you structure the “Holiday” table or column inside the date’s table then tweak the context accordingly inside each variables i.e. something like this - “‘Date Table’[Holiday] = TRUE” or the naming convention that you specify in your file accordingly.

Real Working Days - Harsh 2 = 
VAR _Mobilization_Calculation = 
CALCULATE( COUNTROWS( 'Date Table' ) , 
    DATESBETWEEN(
        'Date Table'[Date] , 
        SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization Start Date] ) , 
        SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization End Date] ) ) ,
    'Date Table'[DayOfWeekName] <> "Sunday" ,
    ALLSELECTED( 'Plannning and Progress Tracker' ) )

VAR _Site_Access_Calculation = 
IF( 
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization Start Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access Start Date] ) &&
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Mobilization End Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( 'Date Table' ) , 
        DATESBETWEEN(
            'Date Table'[Date] , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access Start Date] ) , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access End Date] ) ) ,
        'Date Table'[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( 'Plannning and Progress Tracker' ) ) )

VAR _Excavation_Calculation = 
IF(
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access Start Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation Start Date] ) &&
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Site Access End Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation End Date] ) ,
    0 , 
    CALCULATE( COUNTROWS( 'Date Table' ) , 
        DATESBETWEEN(
            'Date Table'[Date] , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation Start Date] ) , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation End Date] ) ) ,
        'Date Table'[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( 'Plannning and Progress Tracker' ) ) )

VAR _Demobilization_Calculation = 
IF( 
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation Start Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization Start Date] ) &&
    SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Excavation End Date] ) = SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization End Date] ) , 
    0 , 
    CALCULATE( COUNTROWS( 'Date Table' ) , 
        DATESBETWEEN(
            'Date Table'[Date] , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization Start Date] ) , 
            SELECTEDVALUE( 'Plannning and Progress Tracker'[Actual Demobilization End Date] ) ) ,
        'Date Table'[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( 'Plannning and Progress Tracker' ) ) )

RETURN
_Mobilization_Calculation + _Site_Access_Calculation + _Excavation_Calculation + _Demobilization_Calculation

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Note: Yes, you’ll observe that the results are same but the logic is different i.e. in the case of first measure the start date used to be ignored and from next day onwards it used to consider the day as 1 but in the current measure logic/condition considers the start date itself as 1 and Sunday’s are ignored and therefore overall answer is same.

Thanks and Warm Regards,
Harsh

Date Stuff3 - Harsh v2.pbix (3.7 MB)

cms418 · October 13, 2021, 8:03pm

@Harsh Thanks so much, what do you feel is the most accurate? Seems like we have to go one way or the other.

Harsh · October 13, 2021, 8:08pm

Hello @cms418,

Based on the scenario that you’ve specified, try second measure in your file because yes, by looking at the results that “one single day” is getting compensated in either way but in future since you’re also planning to add a holiday column in your date table second measure will be more useful at that time.

Thanks and Warm Regards,
Harsh

cms418 · October 14, 2021, 7:04pm

Hi @Harsh have a look at measure Real Working Days - Harsh 2.2 as I have now incorporated all the activities. Quite the formula here!

That said - I can now see a couple holes.

What shall I do when fields are left blank? It really inflates the day count. See yellow Mat Demobilization date in excel as it is blank(Removed this activity in Real Working Days - Harsh 2.2 measure) but if you drag in Real Working Days - Harsh 2.1 you will see what I mean.
When activities overlap (such as the yellow highlighted in excel) Actual Pipe Prep and Actual NDE Asmt - how can we count from 6/9 - 6/19 rather than counting them individually and adding extra unwanted days in the final total?

Thanks for the guidance so far - amazing.

Dig-0057.xlsx (10.5 KB)

Date Stuff4.pbix (3.7 MB)

Harsh · October 15, 2021, 12:39am

Hello @cms418,

With regards to your first query, you need to enter the context into the measure as per your business specific requirements i.e. whether you want to consider blanks end dates into the calculation or not. If so, what will be the condition? Blank end dates will be considered as Today? Or Blank end dates will be taken as start date itself? Or if it’s blank then ignore by putting the condition as 0? "I leave that aspect upto you about how you want to approach."

In my current measure, I’ll be considering the blank end dates as the start date itself.

Below is the measure provided for the reference -

Working Days = 


----- Mobilization Calculation -----


VAR _Mobilization = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Mobilization Start Date] ) ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mobilization Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) )


----- Site Access Calculation -----


VAR _Site_Access =
IF( ISBLANK( SELECTEDVALUE( Data[Actual Site Access End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Access Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Access Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Mobilization Start Date] ) = SELECTEDVALUE( Data[Actual Site Access Start Date] ) &&
    SELECTEDVALUE( Data[Actual Mobilization End Date] ) = SELECTEDVALUE( Data[Actual Site Access End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Mobilization Start Date] ) = SELECTEDVALUE( Data[Actual Site Access Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Access Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Access End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Mobilization Start Date] ) >= SELECTEDVALUE( Data[Actual Site Access Start Date] ) &&
    SELECTEDVALUE( Data[Actual Mobilization End Date] ) <= SELECTEDVALUE( Data[Actual Site Access End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Access Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Access End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Mobilization ,
IF( 
    SELECTEDVALUE( Data[Actual Mobilization End Date] ) = SELECTEDVALUE( Data[Actual Site Access End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Access Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Access End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Site Preparation Calculation -----


VAR _Site_Preparation = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Site Access Start Date] ) = SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Site Access End Date] ) = SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Site Access Start Date] ) = SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Site Access Start Date] ) >= SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Site Access End Date] ) <= SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Site_Access ,
IF( 
    SELECTEDVALUE( Data[Actual Site Access End Date] ) = SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Site Preparation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Excavation Calculation -----


VAR _Excavation = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Excavation End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Excavation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Excavation Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) = SELECTEDVALUE( Data[Actual Excavation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Site Preparation End Date] ) = SELECTEDVALUE( Data[Actual Excavation End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) = SELECTEDVALUE( Data[Actual Excavation Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Excavation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Excavation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Site Preparation Start Date] ) >= SELECTEDVALUE( Data[Actual Excavation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Site Preparation End Date] ) <= SELECTEDVALUE( Data[Actual Excavation End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Excavation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Excavation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Site_Preparation ,
IF( 
    SELECTEDVALUE( Data[Actual Site Preparation End Date] ) = SELECTEDVALUE( Data[Actual Excavation End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Excavation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Excavation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Initial Coating Calculation -----


VAR _Initial_Coating_Inspection = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Excavation Start Date] ) = SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) &&
    SELECTEDVALUE( Data[Actual Excavation End Date] ) = SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Excavation Start Date] ) = SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Excavation Start Date] ) >= SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) &&
    SELECTEDVALUE( Data[Actual Excavation End Date] ) <= SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Excavation ,
IF( 
    SELECTEDVALUE( Data[Actual Excavation End Date] ) = SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) , 
            SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Pipe Prep Calculation -----


VAR _Pipe_Prep = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) , 
            SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) = SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) &&
    SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) = SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) = SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) , 
            SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Initial Coating Inspection Start Date] ) >= SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) &&
    SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) <= SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) , 
            SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Initial_Coating_Inspection ,
IF( 
    SELECTEDVALUE( Data[Actual Initial Coating Inspection End Date] ) = SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) , 
            SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- NDE Asmt Calculation -----


VAR _NDE_Asmt = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) = SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) &&
    SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) = SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ,
    0 ,
IF(
    SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) >= SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) &&
    SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) <= SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Pipe_Prep ,
IF( 
    SELECTEDVALUE( Data[Actual Pipe Prep Start Date] ) = SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF( 
    SELECTEDVALUE( Data[Actual Pipe Prep End Date] ) = SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Repair Calculation -----


VAR _Repair = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Repair End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Repair Start Date] ) , 
            SELECTEDVALUE( Data[Actual Repair Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) = SELECTEDVALUE( Data[Actual Repair Start Date] ) &&
    SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) = SELECTEDVALUE( Data[Actual Repair End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) = SELECTEDVALUE( Data[Actual Repair Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Repair Start Date] ) , 
            SELECTEDVALUE( Data[Actual Repair End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual NDE Asmt. Start Date] ) >= SELECTEDVALUE( Data[Actual Repair Start Date] ) &&
    SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) <= SELECTEDVALUE( Data[Actual Repair End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Repair Start Date] ) , 
            SELECTEDVALUE( Data[Actual Repair End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _NDE_Asmt ,
IF( 
    SELECTEDVALUE( Data[Actual NDE Asmt. End Date] ) = SELECTEDVALUE( Data[Actual Repair End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Repair Start Date] ) , 
            SELECTEDVALUE( Data[Actual Repair End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Post Repair Calculation -----


VAR _Post_Repair_Asmt = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Repair Start Date] ) = SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) &&
    SELECTEDVALUE( Data[Actual Repair End Date] ) = SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Repair Start Date] ) = SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Repair Start Date] ) >= SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) &&
    SELECTEDVALUE( Data[Actual Repair End Date] ) <= SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Repair ,
IF( 
    SELECTEDVALUE( Data[Actual Repair End Date] ) = SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) , 
            SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Recoat Calculation -----


VAR _Recoat = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Recoat End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Recoat Start Date] ) , 
            SELECTEDVALUE( Data[Actual Recoat Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) = SELECTEDVALUE( Data[Actual Recoat Start Date] ) &&
    SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) = SELECTEDVALUE( Data[Actual Recoat End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) = SELECTEDVALUE( Data[Actual Recoat Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Recoat Start Date] ) , 
            SELECTEDVALUE( Data[Actual Recoat End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Post Repair Asmt. Start Date] ) >= SELECTEDVALUE( Data[Actual Recoat Start Date] ) &&
    SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) <= SELECTEDVALUE( Data[Actual Recoat End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Recoat Start Date] ) , 
            SELECTEDVALUE( Data[Actual Recoat End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Post_Repair_Asmt ,
IF( 
    SELECTEDVALUE( Data[Actual Post Repair Asmt. End Date] ) = SELECTEDVALUE( Data[Actual Recoat End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Recoat Start Date] ) , 
            SELECTEDVALUE( Data[Actual Recoat End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Backfill Calculation -----


VAR _Backfill = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Backfill End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Backfill Start Date] ) , 
            SELECTEDVALUE( Data[Actual Backfill Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Recoat Start Date] ) = SELECTEDVALUE( Data[Actual Backfill Start Date] ) &&
    SELECTEDVALUE( Data[Actual Recoat End Date] ) = SELECTEDVALUE( Data[Actual Backfill End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Recoat Start Date] ) = SELECTEDVALUE( Data[Actual Backfill Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Backfill Start Date] ) , 
            SELECTEDVALUE( Data[Actual Backfill End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Recoat Start Date] ) >= SELECTEDVALUE( Data[Actual Backfill Start Date] ) &&
    SELECTEDVALUE( Data[Actual Recoat End Date] ) <= SELECTEDVALUE( Data[Actual Backfill End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Backfill Start Date] ) , 
            SELECTEDVALUE( Data[Actual Backfill End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Recoat ,
IF( 
    SELECTEDVALUE( Data[Actual Recoat End Date] ) = SELECTEDVALUE( Data[Actual Backfill End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Backfill Start Date] ) , 
            SELECTEDVALUE( Data[Actual Backfill End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Cleanup or Remediation Calculation -----


VAR _Cleanup_or_Remediation = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Backfill Start Date] ) = SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Backfill End Date] ) = SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Backfill Start Date] ) = SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Backfill Start Date] ) >= SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) &&
    SELECTEDVALUE( Data[Actual Backfill End Date] ) <= SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Backfill ,
IF( 
    SELECTEDVALUE( Data[Actual Backfill End Date] ) = SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) , 
            SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Demobilization Calculation -----


VAR _Demobilization = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Demobilization End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Demobilization Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) = SELECTEDVALUE( Data[Actual Demobilization Start Date] ) &&
    SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) = SELECTEDVALUE( Data[Actual Demobilization End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) = SELECTEDVALUE( Data[Actual Demobilization Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Cleanup / Remediation Start Date] ) >= SELECTEDVALUE( Data[Actual Demobilization Start Date] ) &&
    SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) <= SELECTEDVALUE( Data[Actual Demobilization End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Cleanup_or_Remediation ,
IF( 
    SELECTEDVALUE( Data[Actual Cleanup / Remediation End Date] ) = SELECTEDVALUE( Data[Actual Demobilization End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )


----- Mat Demobilization Calculation -----


VAR _Mat_Demobilization = 
IF( ISBLANK( SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ) , 
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) , 
IF( 
    SELECTEDVALUE( Data[Actual Demobilization Start Date] ) = SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) &&
    SELECTEDVALUE( Data[Actual Demobilization End Date] ) = SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ,
    0 ,
IF( 
    SELECTEDVALUE( Data[Actual Demobilization Start Date] ) = SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - 1 , 
IF(
    SELECTEDVALUE( Data[Actual Demobilization Start Date] ) >= SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) &&
    SELECTEDVALUE( Data[Actual Demobilization End Date] ) <= SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) - _Demobilization ,
IF( 
    SELECTEDVALUE( Data[Actual Demobilization End Date] ) = SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ,
    0 ,
    CALCULATE( COUNTROWS( Dates ) , 
        DATESBETWEEN(
            Dates[Date] , 
            SELECTEDVALUE( Data[Actual Mat Demobilization Start Date] ) , 
            SELECTEDVALUE( Data[Actual Mat Demobilization End Date] ) ) ,
        Dates[DayOfWeekName] <> "Sunday" ,
        ALLSELECTED( Data ) ) ) ) ) ) )





RETURN
_Mobilization + _Site_Access + _Site_Preparation + _Excavation + _Initial_Coating_Inspection + _Pipe_Prep + _NDE_Asmt + _Repair + _Post_Repair_Asmt + _Recoat + _Backfill + _Cleanup_or_Remediation + _Demobilization + _Mat_Demobilization

Once you write the above measure, you’ll see that overlapping is ignored. Below is the screenshot of the final results provided for the reference purposes.

I’m also attaching the working of the PBIX file for the reference purposes.

Thanks and Warm Regards,
Harsh

Date Stuff4 - Harsh.pbix (96.4 KB)

Dig-0057 - Harsh.xlsx (14.2 KB)

Harsh · October 15, 2021, 12:42am

Hello @cms418,

Since I ran out of characters/word space here’s the additional or important points mentioned below pertaining to the solution provided above -

Important Notes:

1). In the case of blank end date, the start date itself is considered as a end date. You can modify the logic as per your business scenario/requirements.

2). Wherever Start Date or End Date of the previous criteria and the End Date of the new criteria is same in that case they’re still considered as over-lapping in my measure logic.

3). The entire measure is of more than 600+ lines so if you’re not comfortable with the long measure then in that case you can also break the variables into small individual measures for your understanding purposes. I’ve already placed the comments above each variable which will help you to break-out the measure for analyzing purposes.

4). As per your business logic, you got to add/less or modify the “IF()” conditions inside each variable as per your business scenario’s, if required. The solution already does the heavy lifting since all criteria’s of the dates are considered in this measure now. So if you’ve any additional requirements pertaining to it then modify the logic or conditions as per your requirements.

5). The solution is checked at each and every step based on the logic considerd inside the measure and therefore in case of additional logic please make the changes accordingly as per your business scenario.

Thanks and Warm Regards,
Harsh

cms418 · October 15, 2021, 3:06pm

@Harsh thank you good sir. I learned a lot here as well.

I’ve also never seen a measure of that magnitude!

Harsh · October 15, 2021, 6:38pm

Hello @cms418,

You’re Welcome.

I’m so glad that I was able to assist you on this.

On forum, yes this was the first time I’ve written a measure of that magnitude to provide the solution. I was always under the impression that only Financial Reporting would require such huge measures but with so many date fields in your table your scenario proved me wrong.

Funny thing was today I came to know that onto the forum in one single post one can only write 32,000 characters and I wrote overall 33,106 characters.

Enjoyed working through this one with you.

Thanks and Warm Regards,
Harsh

cms418 · October 15, 2021, 7:35pm

@Harsh
Date Stuff4.pbix (3.7 MB)
LOL amazing stuff, record breakers here!

Put ‘Working Days’ into my model - got a large number. Moved the variable to measure to debug it, Mat demobilization is still getting tripped up. Eyes going crossed trying to see what it is, so much code! hahah, off to the Dentist for now.

cms418 · October 17, 2021, 2:56pm

@Harsh I see what it is. In the actual model both Mat Mob and Mat demob dates are blank. Prob need to put one more ‘If’ in here.

Have a nice day!