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

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'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
      Super 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.