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 pro-rata data based on the days left in the month.

The Matrix would look like this:

ValuesAprMayJunJulAugSepOctNovDecJanFebMarTotals
Monthly Sales645.053837.207955.129811.420354.521       3603.330
Avg Prevs 3 mths     612.750707.023707.023707.023707.023707.023707.0234854.888

 

 

I found how to forecast using the DATESYTD with the End of Financial Year, and got the average to work in one of my attempts by dividing by 3 which is fine but can't quite get it all together.  This is the best example I have so far.

 

CALCULATE(
                  CALCULATE(
CALCULATE(CALCULATE(CALCULATE(SUMX(Sales,DIVIDE(Sales[Amount],Sales[Weighting],0)/1000),FILTER(ALL(Sales[Currency]), Sales[Currency]="AUD")),
DATESBETWEEN('Date'[Date],
                       DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
        DATEADD(LASTDATE ('Date'[Date]),-1,MONTH )                                        ))
   ),FILTER('Date', 'Date'[Date]=EOMONTH(TODAY(),0))),DATESYTD('Date'[Date],"31/3")
      ,FILTER('Date', 'Date'[Month #]=MONTH(TODAY())))/'Date'[DaysinCurrentMonth] *'Date'[DaysLeftCurrentMonth]

Appreciate the help.

 

Binway

Need Help

9 Replies

    • 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