Defining date cycles in a table via a Boolean (PowerBI, Python)

[Anonymous]

Consider the following table consisting of a Date and Item column:

Date	Item
April 2	X
April 3	X
April 5	X
April 11	X
April 12	X
April 14	X
April 20	X
April 2	Y
April 3	Y
April 9	Y
April 11	Y
April 12	Y
April 14	Y

I would like to add an additional column called StartFlag containing Boolean values that indicate if an item "entered" this list, and by "enter" we understand the following:

An item "enters" the list if it was not on the list for the past 5 days.

Hence, the above table should result in the following:

Date	Item	StartFlag
April 2	X	TRUE
April 3	X	FALSE
April 5	X	FALSE
April 11	X	TRUE
April 12	X	FALSE
April 14	X	FALSE
April 20	X	TRUE
April 2	Y	TRUE
April 3	Y	FALSE
April 9	Y	TRUE
April 11	Y	FALSE
April 12	Y	FALSE
April 14	Y	FALSE

Does someone have a solution for this problem (I am also able to run a Python script on my data so that could also lead to a solution.)

Thank you.

[vanessafvg]

you can try a measure like this

StartFlag =
VAR items =
    SELECTEDVALUE ( StartFlag[Item] )
VAR currdate =
    SELECTEDVALUE ( StartFlag[Date] )
VAR maxdate =
    CALCULATE (
        MAX ( StartFlag[Date] ),
        StartFlag[Date] < currdate
            && StartFlag[Item] = items
    )
RETURN
    IF (
        ISBLANK ( maxdate ),
        TRUE (),
        IF ( DATEDIFF ( maxdate, currdate, DAY ) <= 5, FALSE (), TRUE () )
    )

[Anonymous]

Thank you for your response. Could I get it as a calculated column instead of a measure as well?

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=ISBLANK(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Date]>=EARLIER(Data[Date])-5&&Data[Date]<EARLIER(Data[Date]))))

Hope this helps.

[Anonymous]

Would you be able to define this in PowerQuery too? (sorry for this additional question, turns out I couuldn't use it properly via a calculated column in the end)

[Ashish_Mathur]

Why could you not use it properly via a calculated column?  As you can see in my screenshot, it works just fine.

[Anonymous]

My knowledge of PowerBI is quite limited but here is my problem: after adding this flag I want to filter on it and join it with other tables. (The example I gave you is highly simplified.) Those 'table operations' I only manage in PowerQuery unfortunately...