Forum Discussion
Solved
Custer/Flag Rows before Sale by Sale Date
Hi! In my data I have the first 3 columns from the table below, in which the rows are ordered descending by date. According to this data, the customer can make a few applications until eventually gen...
hi AntonN ,
in Power Query, you can duplicate Sale ID column and fill down.
https://learn.microsoft.com/en-us/power-query/fill-values-column
New Column:
Sale ID Distributed =
VAR CurrentDate = 'YourTable'[Date]
VAR CurrentType = 'YourTable'[Type]
RETURN
IF (
CurrentType = "Application",
VAR PreviousSale =
CALCULATE (
MAX ( 'YourTable'[Sale ID] ),
FILTER (
'YourTable',
'YourTable'[Date] < CurrentDate &&
'YourTable'[Type] = "Sale"
)
)
RETURN
IF (
ISBLANK(PreviousSale),
BLANK(),
PreviousSale
),
'YourTable'[Sale ID]
)Once you have created the calculated column using the above formula, you should see the appropriate Sale ID distributed to the Application rows as required.
💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Thanks! It did not work because I have 5 million records and it timed out. However, I looked further and found this:
Solved: How to fill up/down the blanks in a calculated col... - Microsoft Fabric Community
Fill Down =
MAXX (
FILTER (
'table',
'table'[category] = EARLIER('table'[category])
&& 'table'[date] >= EARLIER ( 'table'[date] )
&& NOT ( ISBLANK ( 'table'[ID] ) )
),
'table'[ID]
)
However, it fills the IDs by category up from earlier to later. I want it to do it down from later to earlier. I tried to use "<=" instead of ">=", but it fills only the max ID down to all lines. Any ideas how to fix it?
