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
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 |
Solved! Go to 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.
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.
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)?
Previous flag = maxx(filter(Table, [Id] = earlier([ID]) && [Date] = earlier([date]) -1) , [Product Flag])
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.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
153 | |
137 | |
131 | |
81 | |
61 |