Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Summing values by dynamic date

Hello all!

 

I'm looking to create a custom column or measure that sums up the values of two columns based off a date. Columns are Savings and Forecast.

 

If I have savings January - May, I want to keep a rolling total of that, plus the forecast amount after the current month(current month being May).

 

So if I have 300 dollars of savings year to date up to May, and then I'm forecasted 500 dollars the rest of the year. I want that to add to the total. But when the month changes to June, I want the formula to take the actual savings generated in June. Drop out whatever was forecasted for June, and repeat the sequence above. So it would now take year to date savings until June and then add any months of forecast after June.

 

Essentially it is a rolling addition of actual savings as they occur, along with additon of forecast of anything after the current month. Then dropping out forecast as the savings arrive. 

 

Can anyone help me with the Dax formula to get to what I need?

 

Thank you for your help in advance!

 

Best regards

 

Need Help
  • vivran22's avatar
    vivran22
    5 years ago

    Hello Anonymous 

     

    I have created a sample table for this:

    Month Savings Forecast
    Jan-20 18 14
    Feb-20 20 12
    Mar-20 16 10
    Apr-20 19 11
    May-20 18 14
    Jun-20 14 20
    Jul-20 14 10
    Aug-20 20 20
    Sep-20 11 17
    Oct-20 17 10
    Nov-20 20 19
    Dec-20 18 20

     

    Used the following measures:

     

    YTD Savings = 
   CALCULATE(
       SUM('Table'[Savings]), DATESYTD('Table'[Month])
   )

YTD Forecast = 
CALCULATE(
    SUM('Table'[Forecast]), DATESYTD('Table'[Month])
    )


Sol Measure = 
VAR _CurrentMonth = 
    MAX('Table'[Month])
VAR _LastMonth = DATE(YEAR(_CurrentMonth),12,31)
VAR _RestofYearFilter = 
    FILTER(ALL('Table'[Month]),
         'Table'[Month]>_CurrentMonth
            && 'Table'[Month] <= _LastMonth
    )
VAR _SumRest = 
    CALCULATE(SUM('Table'[Forecast]),_RestofYearFilter)
VAR _AdjustedTotal = 
    [YTD Savings] + _SumRest
RETURN
_AdjustedTotal

     

    Result:

     

    Hope this helps.

     

    Enclosing the sample pbix file.

     

    Cheers!
    Vivek

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter

1 Reply

  • vivran22's avatar
    vivran22
    Community Champion
    5 years ago

    Hello Anonymous 

     

    I have created a sample table for this:

    Month Savings Forecast
    Jan-20 18 14
    Feb-20 20 12
    Mar-20 16 10
    Apr-20 19 11
    May-20 18 14
    Jun-20 14 20
    Jul-20 14 10
    Aug-20 20 20
    Sep-20 11 17
    Oct-20 17 10
    Nov-20 20 19
    Dec-20 18 20

     

    Used the following measures:

     

    YTD Savings = 
   CALCULATE(
       SUM('Table'[Savings]), DATESYTD('Table'[Month])
   )

YTD Forecast = 
CALCULATE(
    SUM('Table'[Forecast]), DATESYTD('Table'[Month])
    )


Sol Measure = 
VAR _CurrentMonth = 
    MAX('Table'[Month])
VAR _LastMonth = DATE(YEAR(_CurrentMonth),12,31)
VAR _RestofYearFilter = 
    FILTER(ALL('Table'[Month]),
         'Table'[Month]>_CurrentMonth
            && 'Table'[Month] <= _LastMonth
    )
VAR _SumRest = 
    CALCULATE(SUM('Table'[Forecast]),_RestofYearFilter)
VAR _AdjustedTotal = 
    [YTD Savings] + _SumRest
RETURN
_AdjustedTotal

     

    Result:

     

    Hope this helps.

     

    Enclosing the sample pbix file.

     

    Cheers!
    Vivek

    Blog: vivran.in/my-blog
    Connect on LinkedIn
    Follow on Twitter