how do i show past data with current and future data in one column?

[sdmikejr]

I feel I am soo very close but maybe not close at all (?!) to resolving this issue and need help. Let me explain:

I have my fact tables, sales and forecast. When the monthly forecast is provided, it is done so with the same grain as the sales table, but for months in the past, the data is not provided. (The grain is rev, qty, sales person, customer, territory, region) As a result, to get a full picture of what the fiscal year is shaping up to be, I need to show actuals for all prior fiscal months, and the forecast for current and future fiscal months remaining. 

I have to believe there is a way of doing this without adding physically adding the historical data (actuals) to the forecast table for each month's forecast, right (?!) 

Apr-Jun needs to be in Rev FC column and Total should include Apr-Jul (please help me!)

[sdmikejr]

Okay-I have figured out how to apply a measure to only one month of sales:

Apr Sales = Calculate(Sum(Sales[ACTUAL_REVNUE]),DATESBETWEEN(Dates[Date Key],DATE(2016,4,1),DATE(2016,4,30)))

Now, I need to now I need to understand how to keep the value from going in every month's row, when all fiscal month's are shown. (Right now, it has the same value in every row).

Any thoughts?

[v-sihou-msft · Accepted solution]

@sdmikejr

If you want to calculate the total ACTUAL_REVNUE for every month, you need to group the data on month level. You suppose to have a Month column in your Dates table, or you can just add a calculated column like: Month(Dates[Date]) to generate a month column. Then you can use ALLEXCEPT to group on month column.

Monthly Sales = Calculate(Sum(Sales[ACTUAL_REVNUE]),ALLEXCEPT(Dates,Dates[Month]))

Regards,