Inventory Forecast using carried forward actual SOH QTY which is a calculated measure.

[KarenQ]

Hi I have problem using the actual SOH total as the start point of month Nov onward for forecast purpose. 

My aim is to use DAX to have all futuer months (Nov onward) show as 71 Qty on hand which is carried forward from last month actual. 

If anyone would help find a solution please? below rawdata sample only. Actual database is very big accross months of past two years showing EOM stock level by item.

https://app.powerbi.com/links/e6Gdg-dA7e?ctid=b3041bfd-b8fd-48d7-8f9d-0d3c53a7e449&pbi_source=linkSh...

[Ahmedx]

pls try

[KarenQ]

Thanks for your prompt reply. 

I tried and had issue with m2

m2 = CALCULATE(MAX('Table'[FullMonth]), FILTER(ALL('Table'),'Table'[Qtr on Hand]>0))

I alternatively used the below and does not work either. 

CurrFullMTH = DATE(YEAR(TODAY()),MONTH(TODAY()-29), 1)

Meanwhile, variation to my orignal post is Qty On Hand against full month 01/10/2023 for example, acutally refers to SOH lelvel for the month before, i.e. Sept. 

I have udpated as per below. 

https://app.powerbi.com/links/e6Gdg-dA7e?ctid=b3041bfd-b8fd-48d7-8f9d-0d3c53a7e449&pbi_source=linkSh...

[Ahmedx]

I can't access it, can you share the file?

[KarenQ]

Resending the below.

Not much difference to the orignal link.

in case you still could not open the link, below rawdata

Item           Qty on hand  FullMONTH  MonthNo

1	2	1/07/2023	1
2	58	1/07/2023	1
3	2	1/07/2023	1
1	3	1/08/2023	2
2	59	1/08/2023	2
3	3	1/08/2023	2
1	4	1/09/2023	3
2	60	1/09/2023	3
3	4	1/09/2023	3
1	5	1/10/2023	4
2	61	1/10/2023	4
3	5	1/10/2023	4
1	6	1/11/2023	4
2	62	1/11/2023	4
3	6	1/11/2023	4
1	0	1/12/2023	6	dummy row
1	0	1/01/2024	7	dummy row
1	0	1/02/2024	8	dummy row
1	0	1/03/2024	9	dummy row
1	0	1/04/2024	10	dummy row
1	0	1/05/2024	11	dummy row
1	0	1/06/2024	12	dummy row

EOM Qty = CALCULATE(SUM(Table1[Qty on hand (incorrect)]), DATEADD(Table1[FullMONTH],1,MONTH))

https://app.powerbi.com/links/e6Gdg-dA7e?ctid=b3041bfd-b8fd-48d7-8f9d-0d3c53a7e449&pbi_source=linkSh...

[Ashish_Mathur]

Hi,

Why are there 0's in the Quantity column for months from Dec onwards?  Shouldn't those be blanks?

[KarenQ]

hi Ashish, please refer to this file. 

https://app.powerbi.com/links/e6Gdg-dA7e?ctid=b3041bfd-b8fd-48d7-8f9d-0d3c53a7e449&pbi_source=linkSh...

I mannaully created Dec onward lines and you could delete it if you do not need them.

Actual quantity aginast fullmonth 01/11/2023 should be the stock level for Oct end of month since these stock valuation reports are ran in the very early hours of the 1st of a new month. 

[Ashish_Mathur]

Hi,

Just to let you know the link that you have shared is not that of a downloadable file.

PBI file attached.

Hope this helps.

[KarenQ]

hi Ashish, thanks for your reply. Seems I could not send a downloadable pbix file. 

Oct is supposed to be latest finalised month which has full month 01/11/2023 against it in the table so latest actual QoH shoud be 65 for Oct and then the same much for future forecast periods. (nb. I had to reference it this way to serve other purposes). 

Below my updated rawdata to replicate my real database.

Item Qty on hand WHS FullMONTH MonthNo

1	2	A	1/07/2023	1
1	1	B	1/07/2023	1
2	58	A	1/07/2023	1
3	2	A	1/07/2023	1
1	3	A	1/08/2023	2
2	59	A	1/08/2023	2
3	3	A	1/08/2023	2
4	0	A	1/08/2023	2
1	4	A	1/09/2023	3
2	0	A	1/09/2023	3
2	60	B	1/09/2023	3
3	0	A	1/09/2023	3
1	0	A	1/10/2023	4
2	61	A	1/10/2023	4
3	1	A	1/10/2023	4
1	1	A	1/11/2023	4
2	62	A	1/11/2023	4
3	2	A	1/11/2023	4

[Ashish_Mathur · Accepted solution]

The answer should be 62 for all months from October onwards.  Revise the QoH measure to:

QoH = if(MAX('Calendar'[Date])>=EOMONTH(today(),-1)+1,CALCULATE([Q],CALCULATETABLE(LASTNONBLANK('Calendar'[Date],CALCULATE([Q])),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),EOMONTH(today(),-1)))),[Q])

Hope this helps.

[KarenQ]

Appreciate your help!

[Ashish_Mathur]

You are welcome.

[Ahmedx]

I can’t help without the file, I don’t know what’s in your matrix
Please note that I have the beginning of each month, and you should have it too

[KarenQ]

hi I have just recreated the file as per below for your help.

Oct is supposed to be latest finalised month which has full month 01/11/2023 against it. (nb. I had to reference it this way to serve other purposes. 

let me know if you could open this link.

as you see m2 and final are not shows as expected. 

https://app.powerbi.com/links/e6Gdg-dA7e?ctid=b3041bfd-b8fd-48d7-8f9d-0d3c53a7e449&pbi_source=linkSh...

Thanks in advance.