Quantifying Total Invoiced Per Month

[ProfessorEgg28]

Hello,

I'm trying to create a monthly budget tracking system that tells me how much money I've invoiced every month. I have a data set that updates twice a month on what purchase orders have been placed and how much of it has been invoiced. An example data set is below: 

Purchaser	Purchase Order	Invoiced Amount	Uninvoiced Amount	Date Modified
Jill	1	10	90	01/01/2024
Jack	2	0	100	01/01/2024
Jill	1	50	50	01/21/2024
Jack	2	100	0	01/21/2024
Jill	1	60	40	02/01/2024
Jill	1	100	0	02/21/2024

The tricky part is, is that sometimes my purchase orders are invoiced in parts. This means that I could potentially pay out an invoice on 3 separate transactions or just one transaction outright. 

In order to accurately calculate the amount of money spent in a month, I think I would need a measure that is able to do something like what I explain below:

IF previous months have same purchase order #, take the difference of the MAX number with that purchase order # of the current entry and MAX value with that purchase order # of the previous months, ELSE value is current month

I'm new to DAX so I'm having a hard time writing this into a new measure. The ideal output would then be:

Purchaser	Purchase Order	Invoiced Amount	Uninvoiced Amount	Date Modified	Spent
Jill	1	10	90	01/01/2024	10
Jack	2	0	100	01/01/2024	0
Jill	1	50	50	01/21/2024	50
Jack	2	100	0	01/21/2024	100
Jill	1	60	40	02/01/2024	10
Jill	1	100	0	02/21/2024	50

With this new column, I'm thinking I would need to search for the MAX values for each of the Purchase Order #s on each month and sum those up to get the total spent for that month. So this measure would look through PO 1 for January and find the max is 50 and then look through PO 2 for January and find the max is 100 and then sum those up to say the total spent in January is 150. Again, not really good at DAX so a hand on that would be nice as well. 

Lastly, I was wondering if I could condense that further and have a value be spit out for the amount spent during the month from a specific person. 

I'm sure there's a more simpler way to do this so any insight would be greatly appreciated. 

Thanks in advanced for the help!

[ProfessorEgg28]

Hello Ryan, 

This is exactly what I want in the new column. Is there any way to modify it so that it doesn't just take from the previous month? I can forsee a situation where no part of the invoice is paid for an entire month. So to elaborate, I could have an invoice for 100 on January, nothing happens in February/March and then it gets partially or fully paid in April. I realize it probably has to do with: 

EDATE(EARLIER('Table'[date]),-1)

so maybe the solution is to modify it so that the filter looks for same purchase orders AND dates one month earlier OR two months earlier OR three months earlier OR four months earlier up to 12 months? Not really sure if there's just an easier way to just say any previous months rather. 

The max column is also super helpful but from this I struggle in creating a measure to sum up on the max values of the same month and also on top of that to sum up all the values of the same month with a specific person. I would be super grateful if you could walk me through this process as well. In an ideal situation I would love to have a slicer that I am able to select which month and which person and it would automatically display how much was spent in that month and or by that person specifically.

Thanks!

Amit, 

I tried using your DAX to create another column but it appears as though it doesn't spit out the correct values. Not sure if I did something wrong myself but below is a screen shot. 

Thanks to you too!

[ryan_mayu]

could you pls update the sample data and expected output? then i will update the solution

[ProfessorEgg28]

Hi Ryan,

What I believe the way the current column only takes into account one month behind. So what I would like for the column to look for all entries with the same PO an infinte number of months behind. So for example, with a dataset where I don't have a partial invoice payment for 3 months would still show that the total for that month is 40.

Purchaser	Purchase Order	Invoiced Amount	Uninvoiced Amount	Date Modified	Spent
Jill	1	10	90	01/01/2024	10
Jack	2	0	100	01/01/2024	0
Jill	1	50	50	01/21/2024	50
Jack	2	100	0	01/21/2024	100
Jill	1	60	40	02/01/2024	10
Jill	1	100	0	05/21/2024	40

Lastly for the PowerBI visual, I would preferably like a dashboard that can display monthly invoice spend and also show it based on the person as well based on a slicer that allows you to select the person. So the graph would show up like: 

If Jill were selected from the slicer it would display this:

And finally if Jack were selected in the slicer it would display:

Thanks!

[ryan_mayu · Accepted solution]

@ProfessorEgg28 

pls see the attachment below

[ryan_mayu]

@ProfessorEgg28 

pls check if this is what you want

create columns

date = date(year('Table'[Date Modified]),month('Table'[Date Modified]),1)

Column =

var _last=maxx(FILTER('Table','Table'[Purchase Order]=EARLIER('Table'[Purchase Order])&&'Table'[date]=EDATE(EARLIER('Table'[date]),-1)),'Table'[Invoiced Amount ])

return if(ISBLANK(_last),'Table'[Invoiced Amount ],'Table'[Invoiced Amount ]-_last)

ifmax = if('Table'[Column]=CALCULATE(max('Table'[Column]),ALLEXCEPT('Table','Table'[Purchase Order],'Table'[date])),"Max")

pls see the attachment below

[amitchandak]

@ProfessorEgg28 , First create a new column

Incremental Invoiced Amount =
VAR CurrentInvoiced = [Invoiced Amount]
VAR PreviousInvoicedMax = CALCULATE(
MAX('Table'[Invoiced Amount]),
[Purchase Order] = EARLIER([Purchase Order]),
[Date Modified] < EARLIER([Date Modified])
)
RETURN
IF(
ISBLANK(PreviousInvoicedMax),
CurrentInvoiced,
CurrentInvoiced - PreviousInvoicedMax
)

Measure 1

Total Monthly Invoiced =
SUMX(
FILTER(
'Table',
MONTH('Table'[Date Modified]) = MONTH(TODAY()) && YEAR('Table'[Date Modified]) = YEAR(TODAY())
),
'Table'[Incremental Invoiced Amount]
)

Measure 2

Total Monthly Invoiced by Purchaser =
CALCULATE(
[Total Monthly Invoiced],
ALLEXCEPT('Table', 'Table'[Purchaser])
)

or

Total Monthly Invoiced by Purchaser =
CALCULATE(
[Total Monthly Invoiced],
filter(all('Table') , 'Table'[Purchaser] = max('Table'[Purchaser]) )
)