Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gwaczoka23
Frequent Visitor

Creating new column with value from previous date

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" ? 🙂 

 

DateIDVendorProduct flagPrevious flag
19.09.20221A1 
19.09.20221B0 
19.09.20221C1 
20.09.20222A01
20.09.20222B10
20.09.20222C01
21.09.20223A10
21.09.20223B11
22.09.20224A11
22.09.20224B01
22.09.20224C10
1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

Hi @gwaczoka23 ,

 

Please add an index column in Power Query:

vyadongfmsft_0-1668647759465.png

 

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:

vyadongfmsft_1-1668648034744.png

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.

View solution in original post

3 REPLIES 3
v-yadongf-msft
Community Support
Community Support

Hi @gwaczoka23 ,

 

Please add an index column in Power Query:

vyadongfmsft_0-1668647759465.png

 

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:

vyadongfmsft_1-1668648034744.png

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
Frequent Visitor

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
Super User
Super User

@gwaczoka23 ,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.