Forum Discussion
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 generating a sale.
What I want to do is to create the 4th column "Sale ID Distributed", where the Sale ID distributes down to the applications before the sale, but only down to the previous sale. In other words, all applications before the sale should be assigned to the sale, but only until the previous sale. Any ideas on how this can be done in DAX?
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
5 Replies
FreemanZSuper User
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
- Kedar_Pande
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?Kedar_Pande , thanks a lot! Can this be done per category?
