Future forecast based on historical average

[amangabeira]

I made a new example and I try to explain step by step:

1. my data is the Sales and Costs columns
2. If I divide the costs by the sales I will get the ratio between this two

The main objective is to forecast the costs of the rest of the year based on the sales that is already in sales column.
I would like to get the average ratio between this two colunmns and use this average to multipli by the future sales.
In the picture I foung the average as 11.86% (jan to apr data)
Then I use DAX to get the real Ratio when I have the costs or the average if blank.

 Then I would use this colum and just multipli by the sales column.

the problem is that when it calculates the Trimester ou Annual my dax is wrong because in scope of trimester it isn't blank so for the second trimester it calculates the respective costs (15) by the total sales of the same period (330) and the final ratio is 4.55%. For the whole year it also not blank and calculates total costs of the year (50) by the total sales of the year (1,410) and the final ratio is 3.55%.

The correct for the second trimester would be the average of 12.5% and the average projected for may and june (11.86% each).

The disired result is:

PBIX:

https://1drv.ms/u/s!Assv7jv6TeY9g5Y0uldq4CCAsPRgqQ?e=rhB0GG

[v-yangliu-msft]

Hi  @amangabeira ,

Excuse me, are you referring to the yellow-labeled data, the current three-month data, not the historical average?

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards,

Liu Yang

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

[amangabeira]

Now I edited the post to try to explain better. Also put a link to the PBIX file. I appreciate the help