Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculate previous month Sales in Virtual Table

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

That TestTable  looks weird

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      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.