Creating new column with value from previous date

[gwaczoka23]

Hi,

Please help me with this issue.

I tried to get the answer here, but I didn't manage to. 
https://community.powerbi.com/t5/Desktop/New-column-with-value-from-previous-last-date/m-p/2880164#M...

I would like to have in new column product flag from previous date (not DAY-1, but last previous) 
I have a report with product IDs, supplier (=vendor) for this product and product flag (doesn't matter what it means now)

In the last column you can find excepted result, notice that I deleted vendor C for ID 3, so previous flag for day 22.09 is getting the data not from 21, but from 20.09

looking quickly at the table, it is easy not to notice that there is a column B with ID, but it's important

Can you please help me to get the formula for column called "Previous flag" ? 🙂 

Date	ID	Vendor	Product flag	Previous flag
19.09.2022	1	A	1
19.09.2022	1	B	0
19.09.2022	1	C	1
20.09.2022	2	A	0	1
20.09.2022	2	B	1	0
20.09.2022	2	C	0	1
21.09.2022	3	A	1	0
21.09.2022	3	B	1	1
22.09.2022	4	A	1	1
22.09.2022	4	B	0	1
22.09.2022	4	C	1	0

[v-yadongf-msft · Accepted solution]

Hi @gwaczoka23 ,

Please add an index column in Power Query:

Please try following DAX to  create two new columns:

Previous index =
CALCULATE (
    MAX ( 'Table'[Index] ),
    FILTER (
        'Table',
        'Table'[Index] < EARLIER ( 'Table'[Index] )
            && 'Table'[Vendor] = EARLIER ( 'Table'[Vendor] )
    )
)

Previous flag_new = LOOKUPVALUE('Table'[Product flag],'Table'[Index],'Table'[Previous index])

You will get the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gwaczoka23]

It's not working 😞 And there is not information about the supplier - it is also important. I have hundreds of them for each products, and there is a lot of products as well.

However, why there is -1 after earlier(date)?

[amitchandak]

@gwaczoka23 ,

Previous flag = maxx(filter(Table, [Id] = earlier([ID])  && [Date] = earlier([date]) -1) , [Product Flag])