Calculate previous month Sales in Virtual Table

[Anonymous]

Hi all!

I'm trying to compare Forecasted Sales vs Actual Sales. Here is some dummy data to explain:

My Sales table showing sales for Sept, Oct and Nov for different periods:

And I have as well a simple tables for Date and Period (Period and PeriodID columns).

My virtual table needs to show the Forecast (current period selected in the filter) in one column, and the Actual (the previous month) in another column. This is because once this work, I'll add from another table the Exchange rates to compare both months.

If I test the measures separatedly, I get the correct results, but if I test the measure with the virtual table, I don't get any results for the Actual using the same formula.

These are the results. It filters correctly the top table, but the bottom one gives blank for the Actual no matter the Period selected.

This is the code I've written. I think the way I'm calculating the previous month is the issue here, but I don't know how to fix it. It works for the Forecast (when I return the forecast option) but not for the Actual:

Test result Actual =

VAR MaxForecast = max(Period[PeriodID])

VAR Forecastpreviousmonth = MaxForecast -1

VAR Exchangerates =

    ADDCOLUMNS(SUMMARIZE(Sales,

               Sales[Continent],

               Sales[Country],

               Sales[Period],

               Sales[PeriodID],

               "Forecast 1", CALCULATE(SUM(Sales[USD]), Sales[PeriodID] = MaxForecast),

               "Actual 1", CALCULATE(SUM(Sales[USD]), ALL(Period), Sales[PeriodID] = Forecastpreviousmonth)),

            "Forecast number", MaxForecast,

            "Actual number", Forecastpreviousmonth)

Return CALCULATE(SUMX(Exchangerates, [Actual 1]))

I can upload the pbix if needed!

Thank you! 🙂

[lbendlin]

use EVALUATEANDLOG to examine each step of your process. You will be able to pinpoint where your filter context is not what you expected it to be.

[Anonymous]

Hi lbendlin,

Thanks for your reply. For what I saw, I need another tool to try and test EVALUATEANDLOG , and I cannot install it in my work laptop, sorry 😞

I've been trying different things and now I see some data for the Actuals, but it shows the total in every month, it's not getting split properly per month. I changed the Period table for the Sales table in the CALCULATE for the Actuals. This is the code:

--------------

__Test Virtual Table Actual =

//VAR Currentperiod2 = SELECTEDVALUE(Period[PeriodID])

//calculate(expression, Filter())

VAR Currentperiod = MAX(Period[PeriodID])

VAR Previousperiod = CALCULATE( MAX(Sales[PeriodID]), ALL(Period[Period]), Period[PeriodID] < Currentperiod)

VAR FXrates =

    ADDCOLUMNS(SUMMARIZE(Sales,

               Sales[Continent],

               Sales[Country],

               Sales[Period],

               Sales[PeriodID],

               'Date'[Month],

               "Test", 1,

               "Forecast 1", CALCULATE(SUM(Sales[USD]), Sales[PeriodID] = Currentperiod),

               "Actual 1", CALCULATE(SUM(Sales[USD]), ALL(Sales), Sales[PeriodID] = Previousperiod)),

            "Forecast number", Currentperiod,

            "Actual number", Previousperiod,

            "Test 2", 2)

Return sumx(FXrates, [Actual 1])

--------------------------------

The "separated measures" is what I should get. When I use those measures inside the virtual table then I get the correct result for the Forecast but not the Actual:

I attach the PBIX if this helps for checking. I cannot upload it directly here so here is the link:

PBIX with Dummy data 

Thank you!

[lbendlin]

That TestTable  looks weird

Are you trying to use current month actuals as prior month forecast?

[Anonymous]

Hi @lbendlin ,

Thank you for your reply,

So each period (PER-X) has the 12 month in each on my real data. Here there are just 3 periods and months to simplify the test.

If the user picks a period, I want to see the sales for each month of that period, but also the sales on the previous month, so I can then compare both. I'm calculating a virtual table because once I have this step done, I'll add some Exchange ratios and other calculations.

Right now I can see correctly the Forecast, in the Test table and in the visual matrix, but the Actual appears correct in the test table but it appears the total amount in each month, instead of getting splitted.

Maybe the approach I'm doing to get this calculation done is not correct and I can do it in a  different way, but I'm not sure how.

Thank you very much for helping. 

[lbendlin]

Think about the sustainability of your process. If you think it is low maintenance then keep using it. But if it is a hassle to set up and you have to constantly change stuff around (now month, new year etc) then it may be worth considering a refactoring. Try to use generally agreed terminology (ie don't mix up actuals and forecast - they have different meaning.  Even the word "forecast"  it self is rather ambiguous.)

See if you can run all this on measures, without the need for the extra table.