Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
Still learining Power BI and not sure where to start with this. Have a list of customers, due dates and invoice amounts. I'm looking to have a chart where by it lists all customers, due date of the day where the most is outstanding and also the amount. I need it not to consider only future values for the next 12 months. Hope i've explained that okay but added some data below to try and explain better.
Any guidance on where to start with this would be appreciated.
Customer | Date | Invoice Amount | |
A | 17/04/2020 | 2 | |
B | 01/05/2020 | 3 | |
C | 06/05/2020 | 5 | |
A | 17/04/2020 | 2 | |
A | 20/04/2020 | 3 | |
A | 30/04/2020 | 10 | |
D | 15/04/2020 | 20 | **in the past so not considered |
E | 30/06/2020 | 2 | |
A | 17/04/2020 | 8 | |
B | 22/05/2020 | 66 | |
C | 06/05/2020 | 3 | |
D | 19/04/2020 | 4 |
Customer | Highest Owed Due Date | Amount |
A | 17/04/2020 | 12 |
B | 22/05/2020 | 66 |
C | 06/05/2020 | 8 |
D | 19/04/2020 | 4 |
E | 30/06/2020 | 2 |
Thank you
Solved! Go to Solution.
Hi @NiugeS ,
You can create a calculated column to add the per day total invoice amount for each customer and then use a measure to find the MAX amount of invoice totals. I have created a sample for this which you can download from here.
You can use the DAX to create a custom column
InvAmount Day = CALCULATE(SUM('Table'[Invoice Amount]),
FILTER('Table', 'Table'[Customer]=EARLIER('Table'[Customer])
&& 'Table'[Date].[Date]=EARLIER('Table'[Date].[Date])))
To calculate the measure for Max amount per day use the below DAX as a new measure
Max Invoice Amount = CALCULATE(MAX('Table'[InvAmount Day]),'Table'[Date]>=TODAY())
If you need any clarification please do let me know.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan Farooqi
Hi @NiugeS ,
You also could try below measures:
Measure =
VAR temp =
SUMMARIZE ( tt, tt[Customer], tt[Date], "sum", SUM ( tt[Invoice Amount] ) )
VAR t1 =
ADDCOLUMNS (
temp,
"max", MAXX (
FILTER ( temp, tt[Customer] = EARLIER ( tt[Customer] ) && tt[Date] > TODAY () ),
[sum]
)
)
RETURN
SUMX (
FILTER ( t1, [max] = [sum] && tt[Customer] = MIN ( tt[Customer] ) ),
[max]
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NiugeS ,
You also could try below measures:
Measure =
VAR temp =
SUMMARIZE ( tt, tt[Customer], tt[Date], "sum", SUM ( tt[Invoice Amount] ) )
VAR t1 =
ADDCOLUMNS (
temp,
"max", MAXX (
FILTER ( temp, tt[Customer] = EARLIER ( tt[Customer] ) && tt[Date] > TODAY () ),
[sum]
)
)
RETURN
SUMX (
FILTER ( t1, [max] = [sum] && tt[Customer] = MIN ( tt[Customer] ) ),
[max]
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @NiugeS ,
You can create a calculated column to add the per day total invoice amount for each customer and then use a measure to find the MAX amount of invoice totals. I have created a sample for this which you can download from here.
You can use the DAX to create a custom column
InvAmount Day = CALCULATE(SUM('Table'[Invoice Amount]),
FILTER('Table', 'Table'[Customer]=EARLIER('Table'[Customer])
&& 'Table'[Date].[Date]=EARLIER('Table'[Date].[Date])))
To calculate the measure for Max amount per day use the below DAX as a new measure
Max Invoice Amount = CALCULATE(MAX('Table'[InvAmount Day]),'Table'[Date]>=TODAY())
If you need any clarification please do let me know.
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan Farooqi
Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
53 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |