Forum Discussion

Trudgeon's avatar
Trudgeon
Icon for Helper III rankHelper III
7 years ago

Measure scenario...is it possible?

Hello,

 

At the Business Applications Summit back in July it was stressed that one should try to always use measures instead of calculated columns, so as not to bog down the system.  I have a scenario that I've been unable to create a measure for and I'm curious if it's even possible.

 

Here is the scenario:

 

We have a forecast each month that extends from the current month till the end of the year.  So the 2018 October Forecast is from Oct to Dec.  We want to compare forecast versions, but it's not apples-to-apples since the October Forecast is 3 months whereas the January Forecast would be for 12 months.  Therefore, I need to create a scenario in which each forecast month includes actuals from the prior months.  So the January amount (12 months of forecast) could be compared against the October amount (3 months of forecast and 9 prior months of actuals).

 

I have already solved this using calculated columns in a matrix, but I needed to create a separate column for each month.  12 calculated columns.  I would love to streamline this into a single measure. 

 

Here is the my DAX function for the October Amount calculated column...

 

October Amount=
IF ( OR ('DATA_TABLE'[FORECAST_VERSION] = "10 Oct",
AND ('DATA_TABLE'[TRANSACTION_PERIOD] < 10,'DATA_TABLE'[FORECAST_VERSION] = "ACTUAL")),
'DATA_TABLE'[SALES_AMOUNT],
0
)

 

Any help would be appreciated.  Thanks!

 

 

8 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support

    Hi Trudgeon,

     

    "We have a forecast each month that extends from the current month till the end of the year.  So the 2018 October Forecast is from Oct to Dec.  We want to compare forecast versions, but it's not apples-to-apples since the October Forecast is 3 months whereas the January Forecast would be for 12 months.  Therefore, I need to create a scenario in which each forecast month includes actuals from the prior months.  So the January amount (12 months of forecast) could be compared against the October amount (3 months of forecast and 9 prior months of actuals)."

     

    <--- Your description of your logic is not so clear, could you share more details about your requirement?

     

    Regards,

    Jimmy Tao

  • Here is some exmaple data, along with expected results...

     

    Raw Data:

     

    VERSIONPRODUCT GROUPPERIODYEARAMOUNT
    01 JAN FORECASTCogs12018450
    01 JAN FORECASTCogs22018350
    01 JAN FORECASTCogs32018400
    01 JAN FORECASTCogs42018600
    01 JAN FORECASTCogs52018500
    01 JAN FORECASTCogs62018600
    01 JAN FORECASTCogs720181000
    01 JAN FORECASTCogs820182000
    01 JAN FORECASTCogs92018850
    01 JAN FORECASTCogs102018750
    01 JAN FORECASTCogs112018800
    01 JAN FORECASTCogs122018850
    01 JAN FORECASTWidgets120185000
    01 JAN FORECASTWidgets220185100
    01 JAN FORECASTWidgets320183000
    01 JAN FORECASTWidgets420185300
    01 JAN FORECASTWidgets520182000
    01 JAN FORECASTWidgets620185500
    01 JAN FORECASTWidgets720185600
    01 JAN FORECASTWidgets820185700
    01 JAN FORECASTWidgets920185800
    01 JAN FORECASTWidgets1020185900
    01 JAN FORECASTWidgets1120186000
    01 JAN FORECASTWidgets1220186100
    02 FEB FORECASTCogs22018275
    02 FEB FORECASTCogs32018275
    02 FEB FORECASTCogs42018400
    02 FEB FORECASTCogs52018400
    02 FEB FORECASTCogs62018400
    02 FEB FORECASTCogs72018600
    02 FEB FORECASTCogs82018600
    02 FEB FORECASTCogs92018600
    02 FEB FORECASTCogs102018800
    02 FEB FORECASTCogs112018800
    02 FEB FORECASTCogs122018800
    02 FEB FORECASTWidgets220184925
    02 FEB FORECASTWidgets320185075
    02 FEB FORECASTWidgets420185225
    02 FEB FORECASTWidgets520185375
    02 FEB FORECASTWidgets620185525
    02 FEB FORECASTWidgets720185675
    02 FEB FORECASTWidgets820185825
    02 FEB FORECASTWidgets920185975
    02 FEB FORECASTWidgets1020186125
    02 FEB FORECASTWidgets1120186275
    02 FEB FORECASTWidgets1220186425
    03 MAR FORECASTCogs32018900
    03 MAR FORECASTCogs42018900
    03 MAR FORECASTCogs52018900
    03 MAR FORECASTCogs62018900
    03 MAR FORECASTCogs72018900
    03 MAR FORECASTCogs82018900
    03 MAR FORECASTCogs92018900
    03 MAR FORECASTCogs102018900
    03 MAR FORECASTCogs112018900
    03 MAR FORECASTCogs1220181500
    03 MAR FORECASTWidgets320185500
    03 MAR FORECASTWidgets420185500
    03 MAR FORECASTWidgets520185500
    03 MAR FORECASTWidgets620185500
    03 MAR FORECASTWidgets720185500
    03 MAR FORECASTWidgets820185500
    03 MAR FORECASTWidgets920185500
    03 MAR FORECASTWidgets1020185500
    03 MAR FORECASTWidgets1120185500
    03 MAR FORECASTWidgets1220185500
    ACTUALWidgets120181000
    ACTUALWidgets12018200
    ACTUALWidgets120181000
    ACTUALWidgets12018450
    ACTUALWidgets120182000
    ACTUALWidgets12018300
    ACTUALCogs1201845
    ACTUALCogs12018450
    ACTUALCogs1201835
    ACTUALWidgets220183500
    ACTUALWidgets220181000
    ACTUALWidgets22018450
    ACTUALCogs22018100
    ACTUALCogs22018150
    ACTUALCogs2201875
    ACTUALWidgets320183500
    ACTUALCogs3201850
    ACTUALCogs3201860

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User

      Hi,

       

      The figure shown in your expected result image of your previous post does not tally with the data you have pasted.  Actual for widgets for Jan ans Feb is 13,400.  Forecast for widgets in March for the balance 10 months is 55,000.  The total should be 68,400.  Your result is 64,450.  How does one explain that difference?

      • Trudgeon's avatar
        Trudgeon
        Icon for Helper III rankHelper III

        According to the numbers I'm seeing, actual for widgets for Jan & Feb is 9,900 and actual for widgets for Jan & Feb & Mar is 13,400.  Therefore, my expected result is 64,450...March for the balance 10 months plus 9,900.  None of the March actuals would be included in the expected results.  I should not have included them in the sample data since it only serves to make it more confusing.