DAX Formulae to pull in Actual and Forecast Data by month depending on reporting month.

[GSM]

HI,

Im trying to build a report which will pull in the actuals from one table up to a set month end date and than forecast by month from forecast table for future months.  I thought best way would be to have a seperate parameter table with current month end date. Than some sort of formulae to pull actual data for any data with dates less than or equal to month end date and than forecast data with any dates after month end date.   Still need to keep prior month forecast data to allow comparison of current month actuals vs forecast.  Cant seem to get the dax formulae to work.

Or can i use a slicer which adjusts the DAX formulae?

Cheers!!

[isaarke]

Hi,

I have a table where I need to input opening balance changes in a given month. The table I am working with has all forecasted data from Jan 1, 2018 to December 2018. If in a given month, my opening balance changes, so I need to update the closing balance for specific month and see what the rest of the year will look like, if I were to update for instance the values for the month of May  2018.

The What if senario seems to be a potential option, but I am not familiar with 'What if Scenario' in reagards to dates. I would like to have the same funtionilty as the 'what if scenario' funtion found in Excel 2016.

Thanks,

Isaac 

[v-huizhn-msft]

Hi @GSM,

You can create a Calendar table using Calendar() function. Then create a slicer including Calendar[Date]. Create a measure to get the date selected in slicer.

Selected=CALCULATE(MAX(Calendar[Date]),ALLSELECTED(Calendar))

Then you can use the measure in filter in your DAX formula to caculate the Actual and Forecast Data.

If this is not want you want, please post some sample data for further analysis.

Best Regards,

Angelia

[jpt1228]

Hello, I am searching for the same - But I don't believe the question was around date table. I have a table of actual invoiced sales dollars and I have a different table for forecast dollars. I want to be able to have a report that brings in actual sales to a date that I filter on and then the forecast dollars for the months that I do not have actuals for to determine where I will finish the year.

So in the below example I am filtering on March 2017 and the report brings sales for Jan-Mar and then Forecast from Apr-Dec. Or if it is easier to have a formula to filter automatically to look at the current month and bring in sales dollars for all the months prior and then for the current month through the ond of the year forecast dollars.