Issue with totals in forecast

[CJensen]

Hi Everybody

Im dealing like everyone else with why my totals dosnt add up, but I cant for the life of me figure out what is wrong. 

Im trying to forecast subscription consumption for X of months based on the sales of the subscription. E.x. the subscription is billed for 12 months, but the purchase invoices comes monthly. I want to find the forecast for purchases for the rest of the year. Right now im trying to get the number right for 2024, before moving forward. 

Any help would be much appreciated

Forecast WORKGODDAMNIT =
var InvoicePeriode = CALCULATE(SUMX(VALUES(Invoices[Sub Periode]),Invoices[Sub Periode]), Invoices[DocumentType] = "Sales")
var eoYEAR = CALCULATE(ENDOFYEAR(Invoices[PostingDate]), Invoices[DocumentType] = "Sales")
var PostDate = CALCULATE(MIN(Invoices[PostingDate]), Invoices[DocumentType] = "Sales")
var calcmonths = DATEDIFF(PostDate, eoYEAR, MONTH)
var NoOfPurch = CALCULATE(COUNTX(Invoices,Invoices[Description]), Invoices[DocumentType] = "Purchase")
var sumofpurch = CALCULATE(SUMX(VALUES(Invoices[Amount]),Invoices[Amount]),Invoices[DocumentType] = "Purchase")
var avgcost = sumofpurch / NoOfPurch

RETURN
calcmonths*avgcost

Example dataset looks like this

DocumentType JournalID Description Qty Amount Sub ID SerialNo Postingdate Sub Periode
Purchase 125070 Subscription Item 1 1 5.583,00 837 A00004991 10-01-2024 00:00 1
Purchase 125070 Subscription Item 2 1 108,00 837 A00004990 10-01-2024 00:00 1
Purchase 125649 Subscription Item 1 1 5.583,00 837 A00004991 13-02-2024 00:00 1
Purchase 125649 Subscription Item 2 1 108,00 837 A00004990 13-02-2024 00:00 1
Sales 216499 Subscription Item 1 1 128.346,00 837 A00004991 03-01-2024 00:00 12
Sales 216499 Subscription Item 2 1 2.350,00 837 A00004990 03-01-2024 00:00 12

[bhanu_gautam]

@CJensen Try using

Forecast_WORKGODDAMNIT =
VAR eoYEAR = CALCULATE(ENDOFYEAR(Invoices[PostingDate]), Invoices[DocumentType] = "Sales")
VAR PostDate = CALCULATE(MIN(Invoices[PostingDate]), Invoices[DocumentType] = "Sales")
VAR calcmonths = DATEDIFF(PostDate, eoYEAR, MONTH)
VAR NoOfPurch = CALCULATE(COUNTX(Invoices, Invoices[Description]), Invoices[DocumentType] = "Purchase")
VAR sumofpurch = CALCULATE(SUMX(VALUES(Invoices[Amount]), Invoices[Amount]), Invoices[DocumentType] = "Purchase")
VAR avgcost = sumofpurch / NoOfPurch

RETURN
calcmonths * avgcost

[CJensen]

Hi Bhanu
Thank you so much for your help, it has helped getting me on the right way. 

I think the problems is that the calculation acutally does what ive asked it to, but not want I want to. It shows the correct value for each line, but the total is the average and not the total. Im not sure on how to forward from here.

[v-zhangtin-msft]

Hi, @CJensen 

Based on the example data you provided, what would you expect the output to be? It can be displayed in an excel format.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.