Forum Discussion

Louke's avatar
Louke
Frequent Visitor
3 years ago

Filter customer with negative total and group datedif

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Louke
Frequent Visitor
3 years ago

Hey Anonymous ,

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":

GroupAmount
< -10 days200,00
-10 to -1 days485,14