Get consecutive dates and calculate sum (exclude weekend)

[powerflanker]

Hi All,

I have a problem statement. I need to calculate sum for consecutive "holiday "(>=3) not "Project" per month. Explained the problem in the image below. Please note that we need to maintain continuous steak excluding weekend (example count continous from 1st July to 7th July as shown below)

Please help me with a DAX formula to get the desired result

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

please check the below measure and the attached pbix file.

expected measure: = 
VAR _calendartable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
            "@day", FORMAT ( [Date], "ddd" )
        ),
        NOT ( [@day] IN ( { "Sat", "Sun" } ) )
    )
VAR _newtable =
    ADDCOLUMNS (
        FILTER ( Data, Data[Work Type] = "Holiday" ),
        "@prevworkdate", MAXX ( FILTER ( _calendartable, [Date] < Data[Fiscal Date] ), [Date] ),
        "@prevdate",
            MAXX (
                FILTER (
                    Data,
                    Data[Work Type] = "Holiday"
                        && Data[Fiscal Date] < EARLIER ( Data[Fiscal Date] )
                ),
                Data[Fiscal Date]
            )
    )
VAR _addflag =
    ADDCOLUMNS ( _newtable, "@flag", IF ( [@prevdate] = [@prevworkdate], 0, 1 ) )
VAR _cumulateflag =
    ADDCOLUMNS (
        _addflag,
        "@cumulateflag",
            SUMX (
                FILTER ( _addflag, Data[Fiscal Date] <= EARLIER ( Data[Fiscal Date] ) ),
                [@flag]
            )
    )
VAR _addcount =
    ADDCOLUMNS (
        _cumulateflag,
        "@count",
            COUNTROWS (
                FILTER ( _cumulateflag, [@cumulateflag] = EARLIER ( [@cumulateflag] ) )
            )
    )
VAR _filteroverthree =
    FILTER ( _addcount, [@count] >= 3 )
RETURN
    IF ( HASONEVALUE ( Data[Full Name] ), SUMX ( _filteroverthree, Data[Rate] ) )

[powerflanker]

Hi @Jihwan_Kim Thank you so much. The solution wroks.

However, If I want to roll up the data by Employee Type Or Project Name like below

Project	Total Cost
Project 1	Total Cost of all employees who have more than 3 consecutive holidays
Project 2	Total Cost of all employees who have more than 3 consecutive holidays

Then, nothing is rendered. It looks like the measure works only when Employee Full name is included in columns.

Could you advise how I can see the measure working when I just include other top level hierarchy fields like Project Name , Project Department etc..

Thank you!

[Jihwan_Kim]

Hi,

Thank you very much for your message.

I added project name into the sample pbix file, and changed a sample file's [Rate] column's numbers.

Please check if it shows the expected numbers.

If your dataset contains more hierachy levels, please share your file and then I can try to look into it.

Thank you. 

[powerflanker]

Hi @Jihwan_Kim 

Thank you so much for tweaking the measure and sharing.

I tried including the measure on my model and see project level countings incorrect. I have tweaked data and given an example in attached file 

PBI File 

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

Thank you very much for your message.

Please check the attached file if I understood your question correctly.

Thank you.

expected measure: =
VAR _calendartable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
            "@day", FORMAT ( [Date], "ddd" )
        ),
        NOT ( [@day] IN ( { "Sat", "Sun" } ) )
    )
VAR _newtable =
    ADDCOLUMNS (
        FILTER ( Data, Data[Work Type] = "Holiday" ),
        "@prevworkdate", MAXX ( FILTER ( _calendartable, [Date] < Data[Fiscal Date] ), [Date] ),
        "@prevdate",
            MAXX (
                FILTER (
                    Data,
                    Data[Project] = EARLIER ( Data[Project] )
                        && Data[Full Name] = EARLIER ( Data[Full Name] )
                        && Data[Work Type] = "Holiday"
                        && Data[Fiscal Date] < EARLIER ( Data[Fiscal Date] )
                ),
                Data[Fiscal Date]
            )
    )
VAR _addflag =
    ADDCOLUMNS ( _newtable, "@flag", IF ( [@prevdate] = [@prevworkdate], 0, 1 ) )
VAR _cumulateflag =
    ADDCOLUMNS (
        _addflag,
        "@cumulateflag",
            SUMX (
                FILTER (
                    _addflag,
                    Data[Project] = EARLIER ( Data[Project] )
                        && Data[Full Name] = EARLIER ( Data[Full Name] )
                        && Data[Fiscal Date] <= EARLIER ( Data[Fiscal Date] )
                ),
                [@flag]
            )
    )
VAR _addcount =
    ADDCOLUMNS (
        _cumulateflag,
        "@count",
            COUNTROWS (
                FILTER (
                    _cumulateflag,
                    Data[Project] = EARLIER ( Data[Project] )
                        && Data[Full Name] = EARLIER ( Data[Full Name] )
                        && [@cumulateflag] = EARLIER ( [@cumulateflag] )
                )
            )
    )
VAR _filteroverthree =
    FILTER ( _addcount, [@count] >= 3 )
RETURN
    IF ( HASONEVALUE ( Data[Project] ), SUMX ( _filteroverthree, Data[Rate] ) )

[powerflanker]

Hi @Jihwan_Kim 

You are real saviour mate! your solution works like a charm. In the past few days I am breaking my head over calculating the missing days (again 2+ consecutive missing days). Refering to the same file (sample_3), I need to calculate 2+ consecutive missing days for an employee and project and multiple the rate. Cant seem to find success. Can you help. Hope the illustration below helps

Thanks again for all the help.

[Jihwan_Kim · Accepted solution]

Hi,

please check the below measure and the attached pbix file.

expected measure: = 
VAR _calendartable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
            "@day", FORMAT ( [Date], "ddd" )
        ),
        NOT ( [@day] IN ( { "Sat", "Sun" } ) )
    )
VAR _newtable =
    ADDCOLUMNS (
        FILTER ( Data, Data[Work Type] = "Holiday" ),
        "@prevworkdate", MAXX ( FILTER ( _calendartable, [Date] < Data[Fiscal Date] ), [Date] ),
        "@prevdate",
            MAXX (
                FILTER (
                    Data,
                    Data[Work Type] = "Holiday"
                        && Data[Fiscal Date] < EARLIER ( Data[Fiscal Date] )
                ),
                Data[Fiscal Date]
            )
    )
VAR _addflag =
    ADDCOLUMNS ( _newtable, "@flag", IF ( [@prevdate] = [@prevworkdate], 0, 1 ) )
VAR _cumulateflag =
    ADDCOLUMNS (
        _addflag,
        "@cumulateflag",
            SUMX (
                FILTER ( _addflag, Data[Fiscal Date] <= EARLIER ( Data[Fiscal Date] ) ),
                [@flag]
            )
    )
VAR _addcount =
    ADDCOLUMNS (
        _cumulateflag,
        "@count",
            COUNTROWS (
                FILTER ( _cumulateflag, [@cumulateflag] = EARLIER ( [@cumulateflag] ) )
            )
    )
VAR _filteroverthree =
    FILTER ( _addcount, [@count] >= 3 )
RETURN
    IF ( HASONEVALUE ( Data[Full Name] ), SUMX ( _filteroverthree, Data[Rate] ) )