cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiugeS
Helper V
Helper V

Finding Due Date of Highest Invoice(s) and Amount

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.

 

CustomerDateInvoice Amount 
A17/04/20202 
B01/05/20203 
C06/05/20205 
A17/04/20202 
A20/04/20203 
A30/04/202010 
D15/04/202020**in the past so not considered
E30/06/20202 
A17/04/20208 
B22/05/202066 
C06/05/20203 
D19/04/20204 

 

 

CustomerHighest Owed Due DateAmount
A17/04/202012
B22/05/202066
C06/05/20208
D19/04/20204
E30/06/20202


Thank you

2 ACCEPTED SOLUTIONS
affan
Solution Sage
Solution Sage

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.

 

Max amount.gif

 

 

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

LinkedIn

 

View solution in original post

dax
Community Support
Community Support

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]
    )

671.PNG

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.

View solution in original post

3 REPLIES 3
dax
Community Support
Community Support

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]
    )

671.PNG

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.

affan
Solution Sage
Solution Sage

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.

 

Max amount.gif

 

 

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

LinkedIn

 

Greg_Deckler
Super User
Super User

Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors