calculate the forecast for a semester from a sales table and a forecast table

[Anonymous]

Dear community, forgive me for my English translated by Google Translation.

I have two tables and a time dimension.

1st table: "Sales" with data from April 2018 to March 2019 is a full fiscal year.

2nd table: "Forecast" with data for June and July 2019 (this is a test game, it is normal that there is no data for the other months).

The scenario would then be:

I click on a month and I wish to have the Forcast of the semester. (example: June, then forecast of the semester 1. November, then forecast of the semester 2) It is therefore necessary to sum the actual results for the months preceding the month selected (if there are months preceding), add the forecast for the current month and finally add the forecast for the following months until the end of the semester.

Do you have any idea of a formula to meet this need? PLEASEEEEEEE

Thank you very much.

[Anonymous]

I'm looking to translate this into DAX.

Declare the selected month, know its position in the semester, add the actual sales of the months (from the table  "Sales ") that precede the month select, append the forecast of the month select (from the table  "Forecast ") and finally add the forecast for the following months until the end of the semester.

Thanks

[TeigeGao]

Hi @Anonymous ,

It's sorry that I can't understand your requirement very well, could you please share the sample data and expected result to us for analysis?

Best Regards,

Teige

[Anonymous]

Hello,

Sorry, I can’t give you a data extract because it’s confidential—

But I will do my best by giving examples.

I have my "Sales" table with details of commercial operations from April 2018 to May 2019
In this column there are: Date, agency, pole, sector, branch, turnover, margin example: June 2018, XX, XXXX, XXXX, XXXX, 1000, 600.

I have a "Forecast" table with estimated forecasts for the turnover and the margin for June and July.
Example: June 2019, XX, XXXX, XXXX, XXXX, 1500, 1200.

The scenario would be: I want to know the forecast for the first half of 2019 [April -> September]
For example, we are looking for the forecast for the first half from June.

So for the math, I need to know:

Which month was selected (June). Its position in the semester (June = 3rd position). Retrieve the sales for the months prior to June (information in the "Sales" table) and add them up. Retrieve the forecast for the current month (June) and finally add the forecast for the following months up to and including September.

When I say "recover forecast", I mean "recover forecast turnover".

I hope I made myself clear.

Thank you.

SAGEAT Corentin