Forum Discussion

Helper I

4 years ago

Solved

Calculate Duration Between Two Dates Based on Multiple Criteria (Activity Status, Holiday, Calendar)

Hi. I have a database that provides me the following information: Project # and Name Activity ID & Name Data Date Start Date (Planned) Finish Date (Planned) Actual Start Actual Finish Baseli...

4 years ago

Hi Taffalaffa ,

Try the following code for the columns (I added on the Base Data Table):

Planned Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (H)],
                            "6 Day", 'Date Table'[6 Day (H)],
                            "7 Day", 'Date Table'[7 Day (H)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (NH)],
                            "6 Day", 'Date Table'[6 Day (NH)],
                            "7 Day", 'Date Table'[7 Day (NH)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        )
)




Act Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Activity Status] = "Not Started", 0,
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (H)],
                                "6 Day", 'Date Table'[6 Day (H)],
                                "7 Day", 'Date Table'[7 Day (H)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (NH)],
                                "6 Day", 'Date Table'[6 Day (NH)],
                                "7 Day", 'Date Table'[7 Day (NH)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        )
)



At Complete Duration =
SWITCH (
    'Base Data'[Activity Status],
    "Completed", 'Base Data'[Act Dur (D)],
    "In Progress", 'Base Data'[Remain Dur (D)] + 'Base Data'[Act Dur (D)],
    "Not Started", 'Base Data'[Remain Dur (D)]
)

Has you can see below result is matching the excel file:

PBIX file attach.

Durations Table (1).pbix304 KB

Taffalaffa
4 years ago
Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!

v-xiaotang

Community Support

4 years ago

Hi Taffalaffa

I create a column,

Test = 
var _type= 'Desired Result (Excel)'[Calendar Type]
var _start='Desired Result (Excel)'[Actual Start]
var _end=IF(ISBLANK('Desired Result (Excel)'[Actual Finish]),TODAY(),'Desired Result (Excel)'[Actual Finish])
return
IF(ISBLANK('Desired Result (Excel)'[Actual Start]) && ISBLANK('Desired Result (Excel)'[Actual Finish]),0,
SWITCH(TRUE(),
_type="5 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end  && 'Date Table'[5 Day (H)]="Workday")),
_type="6 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end  && 'Date Table'[6 Day (H)]="Workday")),
_type="7 Day",CALCULATE(COUNTROWS('Date Table'),FILTER(ALL('Date Table'),'Date Table'[Date]>=_start && 'Date Table'[Date] <=_end  && 'Date Table'[7 Day (H)]="Workday"))))

this column calculate the diff between [Actual Finish] & [Actual Finish], according to calendar type, for example,

and this is the result,

not sure if I understand you correctly, if you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Taffalaffa

Helper I

4 years ago

Hi v-xiaotang and MFelix

I have updated my table / PBI so hopefully it is clearer what I am looking for. V - I am using your custom table currently and have some errors so hopefully this will make my desired result clearer. I think we are close but just not quite there! Here are the new links:

https://www.dropbox.com/s/37pk7k9tq3zok0n/Durations.xlsx?dl=0

https://www.dropbox.com/s/y9v07atrmgbeuyd/Durations%20Table.pbix?dl=0

Thaks you guys for all your help! I really need to get this figured out (hopefully today!!)

- Tiffany

MFelix

Super User

4 years ago

Hi Taffalaffa ,

Try the following code for the columns (I added on the Base Data Table):

Planned Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (H)],
                            "6 Day", 'Date Table'[6 Day (H)],
                            "7 Day", 'Date Table'[7 Day (H)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                'Date Table'[Date] <= 'Base Data'[Finish]
                    && 'Date Table'[Date] >= 'Base Data'[Start]
                    &&
                    VAR Calendar_type =
                        SWITCH (
                            'Base Data'[Calendar Type],
                            "5 Day", 'Date Table'[5 Day (NH)],
                            "6 Day", 'Date Table'[6 Day (NH)],
                            "7 Day", 'Date Table'[7 Day (NH)]
                        )
                    RETURN
                        Calendar_type = "Workday"
            )
        )
)




Act Dur (D) =
SWITCH (
    TRUE (),
    'Base Data'[Activity Status] = "Not Started", 0,
    'Base Data'[Holiday?] = "Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (H)],
                                "6 Day", 'Date Table'[6 Day (H)],
                                "7 Day", 'Date Table'[7 Day (H)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        ),
    'Base Data'[Holiday?] = "No Holidays",
        COUNTROWS (
            FILTER (
                ALL ( 'Date Table' ),
                VAR Status_value =
                    SWITCH (
                        'Base Data'[Activity Status],
                        "Completed", 'Base Data'[Actual Finish],
                        "In Progress", 'Base Data'[Data Date]
                    )
                RETURN
                    'Date Table'[Date] <= Status_value
                        && 'Date Table'[Date] >= 'Base Data'[Actual Start]
                        &&
                        VAR Calendar_type =
                            SWITCH (
                                'Base Data'[Calendar Type],
                                "5 Day", 'Date Table'[5 Day (NH)],
                                "6 Day", 'Date Table'[6 Day (NH)],
                                "7 Day", 'Date Table'[7 Day (NH)]
                            )
                        RETURN
                            Calendar_type = "Workday"
            )
        )
)



At Complete Duration =
SWITCH (
    'Base Data'[Activity Status],
    "Completed", 'Base Data'[Act Dur (D)],
    "In Progress", 'Base Data'[Remain Dur (D)] + 'Base Data'[Act Dur (D)],
    "Not Started", 'Base Data'[Remain Dur (D)]
)

Has you can see below result is matching the excel file:

PBIX file attach.

Durations Table (1).pbix304 KB

Taffalaffa
Helper I
4 years ago
Oh my goodness this is perfect! I truly cannot thank you enough for your help! Thank you so very much!

Forum Discussion

Calculate Duration Between Two Dates Based on Multiple Criteria (Activity Status, Holiday, Calendar)

Related content

Calculating active figures

Creating ICMs via Activator

Fabric pipeline activities web activity Oauth error

Activity Statistics

calculate cumulative sales for active products