Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Louke
Frequent Visitor

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

InvoiceDate

Amount

dueDate

80001878168323.06.2021535,1430.06.2021
80001880119923.11.2020200,0030.11.2020
80001877089922.06.2021-50,0031.12.2021
80005479900215.06.202148,7327.06.2021
80007680120206.02.20211.276,5129.06.2021
80007775209608.06.2021-199,5015.06.2021
80007779420321.06.2021300,0028.09.2021
80009379717617.01.2020-61,8806.02.2020
80009377636325.06.2021605,5901.07.2021
80010277641431.05.20211.936,2521.07.2021
80010278161729.06.2021705,6721.07.2021
80010278257020.06.2021-37,4925.06.2021
80012879668514.05.2021-589,0531.05.2021
80013080002622.06.2021490,7006.07.2021
80013779275714.05.2021303,5510.07.2021
80014162761926.05.2021187,0728.06.2021
80014978910002.06.20212.017,6529.06.2021
80017480144813.06.2021846,3413.06.2021
80017480007418.06.2021498,9025.06.2021
80017480098716.06.2021597,9202.07.2021
80017476429924.06.2021329,9921.07.2021
80017478127617.06.202133,9429.07.2021
80018180081331.05.202148,8716.07.2021
80018980176330.06.2021478,7001.07.2021
80019980039211.05.2021117,7501.07.2021
80024280180203.06.20212.265,7610.06.2021
80024255402830.06.20212.976,8410.07.2021

 

..and I got a slicer for the InvoiceDate:

Louke_0-1663574428673.png

 

 

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

Louke_1-1663574897459.png

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.

Louke_2-1663575682632.png

Result:

Louke_3-1663575703766.png

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!

 

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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

1.png

 

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

vstephenmsft_0-1663659075934.png

 

 

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.

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.