Create a change log of dates

[lafakios]

Hi,

I have a table similar to the below. 

The first objective is to add a flag that will indicate when the Ver value changes, as the 5th column illustrates below

Item	SW	Ver	YYYY.MM	ChangeFlag
ServerA	SWA	1	2020.01
ServerA	SWA	1	2020.02
ServerA	SWA	1	2020.03
ServerA	SWA	1	2020.04
ServerA	SWA	2	2020.05	1
ServerA	SWA	2	2020.06
ServerA	SWA	2	2020.07
ServerB	SWB	1	2020.01
ServerB	SWB	1	2020.02
ServerB	SWB	2	2020.03	1
ServerB	SWB	2	2020.04
ServerB	SWB	2	2020.05
ServerB	SWB	3	2020.06	1
ServerB	SWB	3	2020.07
ServerC	SWC	1	2020.01
ServerC	SWC	1	2020.02
ServerC	SWC	1	2020.03
ServerC	SWC	1	2020.04
ServerC	SWC	1	2020.05
ServerC	SWC	1	2020.06
ServerC	SWC	1	2020.07

The key objective is to create a calculated table of the below structure.

The table should have the change log per item including the pair of Ver and Change Date.

Ideally, even the very first occurence of a ver on an item should be flagged as change (1), so that the initial value will also be included in the change log, as below.

Item	SW	Ver	ChangeDate
ServerA	SWA	1	2020.01
ServerA	SWA	2	2020.05
ServerB	SWB	1	2020.01
ServerB	SWB	2	2020.03
ServerB	SWB	3	2020.06
ServerC	SWC	1	2020.01

Thanks in advance for your support.

Regards,

Akis

[Sahir_Maharaj · Accepted solution]

Hello @lafakios,

Can you please try this approach:

1. Create the ChangeFlag

ChangeFlag = 
VAR CurrentItem = Table[Item]
VAR CurrentSW = Table[SW]
VAR CurrentVer = Table[Ver]
VAR PrevVer = 
    CALCULATE(
        MAX(Table[Ver]),
        FILTER(
            Table,
            Table[Item] = CurrentItem &&
            Table[SW] = CurrentSW &&
            Table[YYYY.MM] < EARLIER(Table[YYYY.MM])
        )
    )
RETURN
IF(ISBLANK(PrevVer) || PrevVer <> CurrentVer, 1, BLANK())

2. Create the Change Log Table

ChangeLog = 
FILTER(
    ADDCOLUMNS(
        Table,
        "ChangeDate", Table[YYYY.MM]
    ),
    NOT(ISBLANK(Table[ChangeFlag]))
)

Hope this helps.

[ryan_mayu · Accepted solution]

@lafakios 

you can try this to creaet a column

Column =

var _date=CALCULATE(min('Table'[YYYY.MM]),ALLEXCEPT('Table','Table'[Item],'Table'[SW],'Table'[Ver]))

return if(_date='Table'[YYYY.MM],1)

[lafakios]

Thanks all to your kind advice and support!

I could manage to successfully build the ChangeLog table.

I created the ChangeFlag column by using the below code (from the second reply)

ChangeFlag =

        var _date = CALCULATE(MIN('Table'[YYYY.MM]), ALLEXCEPT('Table','Table'[Item], 'Table'[SW], 'Table'[Ver]))

return 

        if(_date = 'Table'[YYYY.MM], 1, BLANK() )

And I created the ChangeLog table by using the below code (from the first reply)

ChangeLog = 
FILTER(
    ADDCOLUMNS(
        Table,
        "ChangeDate", Table[YYYY.MM]
    ),
    NOT(ISBLANK(Table[ChangeFlag]))
)

Once again, many thanks for your support.

[Sahir_Maharaj · Accepted solution]

Hello @lafakios,

Can you please try this approach:

1. Create the ChangeFlag

ChangeFlag = 
VAR CurrentItem = Table[Item]
VAR CurrentSW = Table[SW]
VAR CurrentVer = Table[Ver]
VAR PrevVer = 
    CALCULATE(
        MAX(Table[Ver]),
        FILTER(
            Table,
            Table[Item] = CurrentItem &&
            Table[SW] = CurrentSW &&
            Table[YYYY.MM] < EARLIER(Table[YYYY.MM])
        )
    )
RETURN
IF(ISBLANK(PrevVer) || PrevVer <> CurrentVer, 1, BLANK())

2. Create the Change Log Table

ChangeLog = 
FILTER(
    ADDCOLUMNS(
        Table,
        "ChangeDate", Table[YYYY.MM]
    ),
    NOT(ISBLANK(Table[ChangeFlag]))
)

Hope this helps.

[lafakios]

Hi,

Thanks for the reply and for the suggested DAX code!

It actually did generate the change flag in the expected way.

The created table looked also good.

When doing some sample check, I came across a few cases like the below

The ChangeFlag was set to 1 without having any change in the Ver attribute.

Would there be any way to further investigate these cases and prevent this from happening?

Item	SW	Ver	YYYY.MM	ChangeFlag
ServerA	SWA	1	2020.01
ServerA	SWA	1	2020.02
ServerA	SWA	1	2020.03	1
ServerA	SWA	1	2020.04	1

Thanks in advance for your help and support.

Regards,

Akis