DAX - Rank continious working days by group

[Mohamed_59]

Hello everyone,

I hope you are fine 🙂 and I need some help.

I'm struggling with the following needs :

"Identify all couples warehouse/items that are more than 3 continuous Working days".

Let's consider the following sample, What I need is the last column called "Flag continuous Working Day by couple warehouse/item" :

date	isWorkingDay	warehouse	item	Flag continuous Working Day by couple warehouse/item
17/08/2022	1	Wh_0	Item_1	1
18/08/2022	1	Wh_0	Item_1	2
22/08/2022	1	Wh_0	Item_1	1
23/08/2022	1	Wh_0	Item_1	2
24/08/2022	1	Wh_0	Item_1	3
25/08/2022	1	Wh_0	Item_1	4
26/08/2022	1	Wh_0	Item_1	5
27/08/2022	0	Wh_0	Item_1
28/08/2022	0	Wh_0	Item_1
29/08/2022	1	Wh_0	Item_1	1
30/08/2022	1	Wh_0	Item_1	2
03/09/2022	0	Wh_0	Item_1
04/09/2022	0	Wh_0	Item_1
05/09/2022	1	Wh_0	Item_1	1
06/09/2022	1	Wh_1	Item_1	1
07/09/2022	1	Wh_1	Item_2	1
08/09/2022	1	Wh_1	Item_2	2
10/09/2022	0	Wh_1	Item_2
11/09/2022	0	Wh_2	Item_2
12/09/2022	1	Wh_2	Item_2	1
13/09/2022	1	Wh_2	Item_2	2
14/09/2022	1	Wh_3	Item_2	1
15/09/2022	1	Wh_3	Item_2	2
16/09/2022	1	Wh_3	Item_2	3

Thank you !

[CNENFRNL]

Simple enough,

For fun only, a showcase of powerful Excel worksheet formula,

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Flag continuous working day by group CC =
VAR _conditiontable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                Data,
                "@prev",
                    MAXX (
                        FILTER (
                            Data,
                            Data[warehouse] = EARLIER ( Data[warehouse] )
                                && Data[item] = EARLIER ( Data[item] )
                                && Data[date] < EARLIER ( Data[date] )
                                && Data[isWorkingDay] = 1
                        ),
                        Data[date]
                    )
            ),
            Data[isWorkingDay] = 1
        ),
        "@condition",
            IF ( [@prev] = BLANK () || INT ( Data[date] - [@prev] ) = 1, 0, 1 )
    )
VAR _grouptable =
    ADDCOLUMNS (
        _conditiontable,
        "@group",
            SUMX (
                FILTER (
                    _conditiontable,
                    Data[warehouse] = EARLIER ( Data[warehouse] )
                        && Data[item] = EARLIER ( Data[item] )
                        && Data[date] <= EARLIER ( Data[date] )
                ),
                [@condition]
            )
    )
VAR _rankbygroup =
    ADDCOLUMNS (
        _grouptable,
        "@rank",
            COUNTROWS (
                FILTER (
                    _grouptable,
                    Data[warehouse] = EARLIER ( Data[warehouse] )
                        && Data[item] = EARLIER ( Data[item] )
                        && [@group] = EARLIER ( [@group] )
                        && Data[date] <= EARLIER ( Data[date] )
                )
            )
    )
RETURN
    MAXX (
        FILTER (
            _rankbygroup,
            Data[date] = EARLIER ( Data[date] )
                && Data[warehouse] = EARLIER ( Data[warehouse] )
                && Data[item] = EARLIER ( Data[item] )
        ),
        [@rank]
    )

[Mohamed_59]

Hello Jihwan_Kim,

Thank you very much but I made a mistake previously on my screen shot.

Actually, this is what I want :

As we have the same item in the same warehouse and as the date 2022-08-29 is the next working day of the day 2022-08-26, the rank have to continue the series.

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback, and please check the below picture and the attached pbix file.

Flag continuous working day by group CC =
VAR _conditiontable =
    ADDCOLUMNS (
        //FILTER (
        ADDCOLUMNS (
            Data,
            "@prev",
                MAXX (
                    FILTER (
                        Data,
                        Data[warehouse] = EARLIER ( Data[warehouse] )
                            && Data[item] = EARLIER ( Data[item] )
                            && Data[date] < EARLIER ( Data[date] ) // && Data[isWorkingDay] = 1
                    ),
                    Data[date]
                )
        ),
        //Data[isWorkingDay] = 1
        //),
        "@condition",
            IF ( [@prev] = BLANK () || INT ( Data[date] - [@prev] ) = 1, 0, 1 )
    )
VAR _grouptable =
    ADDCOLUMNS (
        _conditiontable,
        "@group",
            SUMX (
                FILTER (
                    _conditiontable,
                    Data[warehouse] = EARLIER ( Data[warehouse] )
                        && Data[item] = EARLIER ( Data[item] )
                        && Data[date] <= EARLIER ( Data[date] )
                ),
                [@condition]
            )
    )
VAR _rankbygroup =
    ADDCOLUMNS (
        _grouptable,
        "@rank",
            COUNTROWS (
                FILTER (
                    _grouptable,
                    Data[warehouse] = EARLIER ( Data[warehouse] )
                        && Data[item] = EARLIER ( Data[item] )
                        && [@group] = EARLIER ( [@group] )
                        && Data[date] <= EARLIER ( Data[date] )
                        && Data[isWorkingDay] = 1
                )
            )
    )
RETURN
    IF (
        Data[isWorkingDay] = 0,
        BLANK (),
        MAXX (
            FILTER (
                _rankbygroup,
                Data[date] = EARLIER ( Data[date] )
                    && Data[warehouse] = EARLIER ( Data[warehouse] )
                    && Data[item] = EARLIER ( Data[item] )
            ),
            [@rank]
        )
    )

[Mohamed_59]

Awesome !

Thank you very much Jihwan_Kim.