Create index column and when found 0 it's will start from 1 again

[Siriwat_Lukin]

Hello

I'm Siriwat, I try to create index column as example table below (from Excel)

I have 1 column "Work Day" and  i try to create result column in PBI but not successful.

Could you please help me to solve or have any suggestions?

Thank in advance^^

[Siriwat_Lukin]

Dear Jihwn_Kim

The answer is correct.

Thank you very much. i will learn from you formula and try to understand.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your actual data model looks like, but I tried to create a sample pbix file like below.

It is for creating a new column.

Please check the below Calculated Column and attached pbix file.

Result CC =
VAR _daytable =
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 1, 31 ) ),
        "@daynumber", DAY ( [Date] )
    )
VAR _addDATAtable =
    ADDCOLUMNS (
        _daytable,
        "@WorkDay", MAXX ( FILTER ( Data, Data[Work Day] = [@daynumber] ), Data[Work Day] )
    )
VAR _addpreviousdaynumber =
    ADDCOLUMNS (
        _addDATAtable,
        "@previousdaynumber",
            MAXX (
                FILTER ( _addDATAtable, [@daynumber] < EARLIER ( [@daynumber] ) ),
                [@daynumber]
            )
    )
VAR _addflag =
    ADDCOLUMNS (
        _addpreviousdaynumber,
        "@flag",
            IF (
                MAXX (
                    FILTER (
                        _addpreviousdaynumber,
                        [@daynumber] = EARLIER ( [@previousdaynumber] )
                    ),
                    [@WorkDay]
                )
                    = BLANK (),
                1,
                0
            )
    )
VAR _addgroupflag =
    ADDCOLUMNS (
        _addflag,
        "@group", SUMX ( FILTER ( _addflag, [@daynumber] <= EARLIER ( [@daynumber] ) ), [@flag] )
    )
VAR _addnumberflag =
    ADDCOLUMNS ( _addgroupflag, "@numberflag", IF ( [@WorkDay] <> BLANK (), 1, 0 ) )
VAR _addresult =
    ADDCOLUMNS (
        _addnumberflag,
        "@result",
            IF (
                [@WorkDay] = BLANK (),
                BLANK (),
                SUMX (
                    FILTER (
                        _addnumberflag,
                        [@group] = EARLIER ( [@group] )
                            && [@daynumber] <= EARLIER ( [@daynumber] )
                    ),
                    [@numberflag]
                )
            )
    )
RETURN
    MAXX (
        FILTER (
            SUMMARIZE ( _addresult, [@daynumber], [@WorkDay], [@result] ),
            [@WorkDay] = Data[Work Day]
        ),
        [@result]
    )

[Siriwat_Lukin]

Hello Kh.Jihwan_Kim,

I have some problem when datatable add some data and condition as table below :

I try to modify from yours reply but can't solve it.

Could you please suggest for solve this problems ?

Thank in advance again.

[Jihwan_Kim]

Hi,

If you have a date column together, the solution might be simpler.

Please check the attached file and the below DAX formula.

It is for creating a new column.

Rank result CC = 
VAR _currentdate = Data[Date]
VAR _currentname = Data[Name]
VAR _flagtable =
    ADDCOLUMNS ( Data, "@flag", IF ( Data[Work/Off] <> BLANK (), 0, 1 ) )
VAR _groupingtable =
    ADDCOLUMNS (
        _flagtable,
        "@group",
            SUMX (
                FILTER (
                    _flagtable,
                    Data[Date] <= EARLIER ( Data[Date] )
                        && Data[Name] = EARLIER ( Data[Name] )
                ),
                [@flag]
            )
    )
VAR _ranking =
    ADDCOLUMNS (
        _groupingtable,
        "@rank",
            IF (
                Data[Work/Off] <> BLANK (),
                RANKX (
                    FILTER (
                        _groupingtable,
                        Data[Name] = EARLIER ( Data[Name] )
                            && [@group] = EARLIER ( [@group] )
                            && Data[Work/Off] <> BLANK ()
                    ),
                    Data[Date],
                    ,
                    ASC
                )
            )
    )
RETURN
    MAXX (
        FILTER ( _ranking, Data[Date] = _currentdate && Data[Name] = _currentname ),
        [@rank]
    )

[Siriwat_Lukin]

Dear

It's work, Thank you very much 😊