Removing unwanted Sales data

Power Query
1

Hi

I’m having trouble removing some unwanted Sales data from my model. These unwanted transactions are giving me incorrect results in my visualisations.

I want to remove any Sales for a Customer that occur before the start of a monthly recurring gift for that customer.

For example, the customer below has five sales in the data occurring prior to the start of a new recurring payment that started on 01/04/2021.

I need to strip out those sales occurring prior to the pledge start date.

Thanks

Pledges Sample 21-09-2023.pbix (26.6 KB)
SampleData 21-09-2023.xlsx (16.3 KB)

image
image962×191 7.01 KB

2

hello
a possible solution is by adding a additional column using the dax code

select =
CALCULATE (
VAR customer =
VALUES ( ‘Sample Data’[Customer ID] )
VAR soldDATE =
VALUES ( ‘Sample Data’[Sold Date] )
VAR pledgedate =
VALUES ( ‘Sample Data’[Pledge Start Date] )
RETURN
MAXX (
FILTER ( ‘Sample Data’, [Customer ID] = customer && soldDATE < pledgedate ),
“do not select”
)
)
I add the pibx with the solution
there are of course other solution
regards
Roger
Pledges Sample 21-09-2023 possible solution.pbix (27.5 KB)

3

Hi Roger

Thank you for this solution.

I’ll test it later today.

4

Hi

Roger’s solution worked perfectly.

However, I was hoping not to bring the unwanted sales into my data model.

Would anyone know the M Code that would achieve the same result?

Thanks

5

Johnatan

you can wrap it in a virtual table and filter the wanted data and hide the original sample table
see ( screen shot )

kind regards
roger

image
strong text

6

Jonathan

attached the solution in power query

kind regards
Roger

SampleData 21-09-2023 solution in Power Qury.xlsx (30.1 KB)