Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 ...
Need Help
  • vivran22's avatar
    vivran22
    6 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

vivran22's avatar
vivran22
Icon for Community Champion rankCommunity Champion
6 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