Power BI calculated table, new column that adds 1 to previous row value based on condition

[LindaO713]

Hello: I'm new to Power BI and need a little help. I have searched for what I'm trying to accomplish but haven't quite found a suitable solution.

I have a calculated table in Power BI. I'm trying to create a New Column that does the following: If the difference between pickup and next pickup is >90 then previous row value + 1, or else previous row value.

New Column = IF('TABLE_All Data Combined'[Days Between Pickup & NextPickup]> 90,"Prev Row Value + 1","Prev Row Value")

Below is an example of what I would the results to look like. Any help would be greatly appreciated

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and please try something like below.

Expected result newe column CC = 
VAR _startrow =
    MINX( FILTER( Data, Data[Cust ID] = EARLIER( Data[Cust ID])), Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

[Jihwan_Kim]

Hi, 

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Expected result newe column CC =
VAR _startrow =
    MIN ( Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

[LindaO713]

Hi Jihwan - THANK YOU so much!!!! This almost works! However, I failed to mention that this table has many customers, so I need it to start at 1 for min pickup date for each customer (hope below in "Expected Cust Grouping" column helps demonstate what I'm looking to do)

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message, and please try something like below.

Expected result newe column CC = 
VAR _startrow =
    MINX( FILTER( Data, Data[Cust ID] = EARLIER( Data[Cust ID])), Data[PickUp Date] )
VAR _addcolumn =
    ADDCOLUMNS (
        Data,
        "@condition",
            SWITCH (
                TRUE (),
                Data[PickUp Date] = _startrow, 1,
                DATEDIFF ( Data[Next Pickup], Data[PickUp Date], DAY ) > 90, 1,
                0
            )
    )
RETURN
    SUMX (
        FILTER (
            _addcolumn,
            Data[PickUp Date] <= EARLIER ( Data[PickUp Date] )
                && Data[Cust ID] = EARLIER ( Data[Cust ID] )
        ),
        [@condition]
    )

[LindaO713 · Accepted solution]

Jihwan!!! Thank you so much for all your help, this works perfectly!!! I thought I needed to change the VAR startrow to Minx(Filter, but was playing around with it for a while and couldn't figure it out. Your help is greatly appreciated!