Creating a summary table which calculates the latest inventory available for each month

[Courtney]

Hi, 

I am trying to calculate the latest "Amount" per each "Account" for each month. In the following example, you can consider the "Date_Undertaken" as an inventory check for each of each "Account", and for each month in time, I would like to know the total "Amount" or inventory available. 

I do not care for the day of the months, so the day specified in the "Date_Undertaken" column can be ignored and just the month the inventory was checked is to be used. The "Material" is not important at this stage. 

In some months no inventory checks maybe undertaken, other months may have many. Although the inventory checks should be undertaken on an annual basis, the date of the actual inventory check varies. I want the output to specify the total inventory for each month from 01/01/2022, even when no inventory checks have been undertaken in a given month. 

For example, if I have the following data:

Reporting_Period	Date_Undertaken	Account	Material	Amount
1/01/2022	3/01/2022	1	A	900
1/01/2022	3/01/2022	1	B	1100
1/02/2022	5/03/2022	3	A	100
1/02/2022	5/03/2022	3	B	150
1/02/2022	5/03/2022	3	C	150
1/04/2022	7/04/2022	2	D	3000
1/04/2022	7/04/2022	2	E	3700
1/05/2022	16/05/2022	4	A	80
1/05/2022	16/05/2022	4	D	20
1/01/2023	4/01/2023	1	C	50
1/01/2023	4/01/2023	1	E	50
1/01/2023	4/01/2023	1	F	50
1/01/2023	4/01/2023	1	G	50
1/02/2023	5/03/2023	3	A	1000
1/02/2023	5/03/2023	3	C	3000
1/02/2023	5/03/2023	3	E	1020
1/05/2023	3/05/2023	4	G	1000
1/05/2023	3/05/2023	4	H	2400

I am trying to create the following Summary table, to give me a running total of all inventory available as an output:

Reporting_Period	Amount
1/01/2022	2000
1/02/2022	2000
1/03/2022	2400
1/04/2022	9100
1/05/2022	9200
1/06/2022	9200
1/07/2022	9200
1/08/2022	9200
1/09/2022	9200
1/10/2022	9200
1/11/2022	9200
1/12/2022	9200
1/01/2023	7400
1/02/2023	7400
1/03/2023	12020
1/04/2023	12020
1/05/2023	15320

I have done a similar summary in R, with the logic being: for each month, filter for the latest inventory check undertaken for each Account, then sum the Amount. 

I'm fairly new to DAX so any help would be appreciated. 

Thank you in advance, 

Courtney

[HarishKM]

@Courtney Hey,
you can use Group by feature in power query.  output will be like this 

M Code will be like this .
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Date"}, {{"Total Sales", each List.Sum([#" Sales"]), type nullable number}})
in
#"Grouped Rows"

Thanks 
HarishM.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[v-jingzhang]

Hi @Courtney 

You can first add a new table with below DAX to have all months in a column. 

Table 2 = FILTER(CALENDAR(MIN('Table'[Reporting_Period]),MAX('Table'[Reporting_Period])),DAY([Date])=1)

Then add a new column in the new table with DAX

Amount = SUMX(FILTER('Table', EOMONTH('Table'[Date_Undertaken],-1)+1 <= 'Table 2'[Date]), 'Table'[Amount])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Courtney]

Hi @v-jingzhang  

Thank you so much for the response. This is really close to what I am after, however isn't quite there yet. 

The part it is missing, is a filter to only select the latest Amounts for each Account. So, when Account 1 undertakes a second inventory check in 1/1/2023, I only want PowerBI to pick up the inventory for Account 1 undertaken in 2023 (undertaken 4/1/23), plus the three stocktakes for Accounts 2 through to 4 during 2022 (i.e. the latest inventory check for each Account). This would mean for the reporting period of January 2023, the Amount should equal 7400. 

And then for March 2023, I would want Power BI to pick up the 2023 inventory for Accounts 1 (undertaken 4/1/23) and 3 (undertaken 5/3/23), and the 2022 inventory for Accounts 2 (undertaken (7/4/22) and 4 (undertaken 16/5/22). The March 2023 Amount should then equal 12020.

I hope that makes some sense? I'm not sure how better to explain what I am looking for.  

(Also note I had a typo in the second table of the inital post - the May 2023 Amount should be 15320)

[v-jingzhang · Accepted solution]

Hi @Courtney 

Yes, this makes sense. Now I undersand the correct expected result. Please use the new column formula as below. 

Amount = 
var _table = FILTER('Table', EOMONTH('Table'[Date_Undertaken],-1)+1 <= 'Table 2'[Date])
var _table2 = SUMMARIZE(_table, 'Table'[Account], "Latest Date", MAX('Table'[Date_Undertaken]))
var _table3 = ADDCOLUMNS(_table2, "Latest Amount", SUMX(FILTER(_table,'Table'[Account]=EARLIER([Account])&&'Table'[Date_Undertaken]=EARLIER([Latest Date])), 'Table'[Amount]))
return
SUMX(_table3, [Latest Amount])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Courtney]

@v-jingzhang  Thank you again for your response. 

I am getting an error message when trying to create the Table "Amount"... It says;

"A single value for column 'Date' in table 'Table 2' cannot be determined. This can happen when a measyre formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result." 

Any thoughts on how to fix that part up?

Thank you again, your efforts are appreciated!

Kind regards, 

Courtney

[v-jingzhang]

Hi @Courtney 

"Amount" is a column not a table. You need to have a Date column in the table first. Please refer to the sample pbix file attached in my last reply. That file has code for the new table and the new column in it. 

Best Regards,

Jing

[Courtney]

Oh, of course. Thank you! That works!! 

Thank you again, 

Kind regards, 

Courtney