Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table with "Date" (=Transaction date), "Start of period" date (chosen here to be 10 days before the Transaction date), Customer ID, and Value. What I would like to do is add a custom column that calculates the sum of "Value" for the specific Customer ID between my transaction date and the "Start of period" date.
Example below:
For the highlighted date (05/08/2020) and highlighted customer (17357) I want Power Query to go back up my Transaction dates until it finds my "Start of period" date, and then sum up the "Value" of all instances of 17357. Like I've highlighted, the customer comes up only once again on 29/07/2020 with Value 0. So the result in my highlighted row (05/08/2020 for customer 17357) should be 0+0 = 0
I think that List.Sum might be part of the solution but I don't know how to add a "Dates.Between" filter to it....
Hoping for somebody much smarter than me who can help me here! Thanks!
@Mr_Glister
Add the following Custom Column:
= Table.AddColumn(#"Changed Type1", "Custom", each List.Sum(
Table.SelectRows(#"Changed Type1",
let
cust = [Customer ID] ,
sdate = [Start of period] ,
edate = [Date]
in
each [Customer ID] = cust and
[Date] >= sdate and
[Date] <= edate
)[Value]))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi there,
I would use a comparable approach like @Fowmy , just for performance reasons I would skip the
each [Customer ID] = cust
part and would apply the rest as a function on the grouped partitions instead, like described here:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF und vielen Dank!
I followed your link and it looks very fancy but unfortunately I still can't quite figure out how to do the things you're suggesting. But I'd really like to know! Would you be able to be a bit more specific about how I would apply your solution? It would be highly appreciated!
Hi @Mr_Glister,
The only power query solution i can think of here is expanding the date interval, merging the table with itself and then group it on the original rows. Do you really need to have this column in Power Query?
@ImkeF , do you by any chance have a less performance heavy solution for this?
/ J
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |