Forum Discussion

Binway's avatar
Binway
Icon for Helper II rankHelper II
8 years ago

Current Month Calc, forecast and Pro rate

Hi Folks, I a financial year starting in April where I need to calculate the current months average for the previous 3 months and forecast to the end of the year, but with the current month showing ...
Need Help
  • Binway's avatar
    Binway
    Icon for Helper II rankHelper II
    8 years ago

    Thanks Cherie it looked like the code and resutls in the "Forecasting Future Months" link would do the job. Your code looks like this:

    Measure =
IF (
    ISBLANK ( SUM ( 'Actual Spend'[Corporate Card Spend] ) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES ( 'Calendar Dates'[Month/Year] ),
            ALLEXCEPT ( 'Calendar Dates', 'Calendar Dates'[Fiscal Year] )
        ),
        CALCULATE ( SUM ( 'Actual Spend'[Corporate Card Spend] ) )
    )
)

    I thouhgt I would start with a simple replication of your code which is not that different:

     

    AA1_FrmDskFrm = 
IF (
    ISBLANK (SUM([Amount]) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES('Date'[Month-year] ),
            ALLEXCEPT ( 'Date','Date'[Financial year])
        ),
        CALCULATE(SUM([Amount]))
    )
)

    But the result is not similiar - have I got to do something different in the Date table perhaps.Below is the result. Sorry just started reading Alberto's Definitive Guide to DAX so have bit to learn. results I get

     

     

     

    Regards

    Binway

  • Binway's avatar
    Binway
    Icon for Helper II rankHelper II
    8 years ago

    Hi Cherie,

    Got the Forecast code working.  Seems my Month/Year needs to be a date.

    Hopefully can tweak this to suite my needs.

     

     

  • Binway's avatar
    Binway
    Icon for Helper II rankHelper II
    8 years ago

    Hi Cherie,

    Got the Forecast code working.  Seems my Month/Year needs to be a date.

    Hopefully can tweak this to suite my needs.

     

     

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Binway

       

      It seems you may need a calendar date table. If it is not your case, please share some data sample file. You can upload it to OneDrive or Dropbox and post the link here.

       

      Regards,

      Cherie

      • Binway's avatar
        Binway
        Icon for Helper II rankHelper II
        8 years ago

        Hi Cherie,

        You may get a better view from the PBIX file - hopefully you can see it.

        Avg Forecast Rpt

        I have broken the calculations down and put them in the matrix along with what would be the final field which presents correctly but the calcualtion is not the average of the previous 3 mths.

         

         

        Thanks Binway