Filter customer with negative total and group datedif

[Louke]

Hello all,

I created a pbix file that hopefully helps to get an answer: Example pbix file 

I got a table with oustanding amounts and invoice numbers per customer:

CustomerID	InvoiceNo	InvoiceDate	Amount	dueDate
800018	781683	23.06.2021	535,14	30.06.2021
800018	801199	23.11.2020	200,00	30.11.2020
800018	770899	22.06.2021	-50,00	31.12.2021
800054	799002	15.06.2021	48,73	27.06.2021
800076	801202	06.02.2021	1.276,51	29.06.2021
800077	752096	08.06.2021	-199,50	15.06.2021
800077	794203	21.06.2021	300,00	28.09.2021
800093	797176	17.01.2020	-61,88	06.02.2020
800093	776363	25.06.2021	605,59	01.07.2021
800102	776414	31.05.2021	1.936,25	21.07.2021
800102	781617	29.06.2021	705,67	21.07.2021
800102	782570	20.06.2021	-37,49	25.06.2021
800128	796685	14.05.2021	-589,05	31.05.2021
800130	800026	22.06.2021	490,70	06.07.2021
800137	792757	14.05.2021	303,55	10.07.2021
800141	627619	26.05.2021	187,07	28.06.2021
800149	789100	02.06.2021	2.017,65	29.06.2021
800174	801448	13.06.2021	846,34	13.06.2021
800174	800074	18.06.2021	498,90	25.06.2021
800174	800987	16.06.2021	597,92	02.07.2021
800174	764299	24.06.2021	329,99	21.07.2021
800174	781276	17.06.2021	33,94	29.07.2021
800181	800813	31.05.2021	48,87	16.07.2021
800189	801763	30.06.2021	478,70	01.07.2021
800199	800392	11.05.2021	117,75	01.07.2021
800242	801802	03.06.2021	2.265,76	10.06.2021
800242	554028	30.06.2021	2.976,84	10.07.2021

..and I got a slicer for the InvoiceDate:

First step: I only need the customers with a positive total:

Second step: The invoices of the customers with positive totals should be grouped after the datediff of a selectable deadline-date and the due date per invoice.

Result:

Both steps could be solved easily if the date is fixed, but it should be selectable.

Each of my measures just filters the invoices with negative amount, but I would like to filter the customers with negative total and after it group the amount by datedif.

Thank you so much in advance!

[v-stephen-msft]

Hi @Louke ,

For you step one, you could create a table visual like the following. Put the two field into it and select the aggregation of amount as the sum. Set up the filter of the amount as "is greater than 0".

For you step two, I don't know the logic of datediff. For example, what is the day interval of customerID 800018?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Louke]

Hey @v-stephen-msft ,

thanks a lot for your reply.

Unfortunately I do not need the table in step one. I just need the logic behind this table.

I need measures to firstly get the customers with a positive total. After that I would like to group the invoices of these customers by the datediff of the dueDate per invoice and the selected deadlineDate.

For the example of customerID 800018, if deadlineDate is "30.06.2021":

Group	Amount
< -10 days	200,00
-10 to -1 days	485,14