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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.