Reset aggregation measure at the start of each year

[Matt22365]

Hi all

I hope you can help

I have a measure to generate a "running total" line chart to show spend over time i.e. when a new spend is made the line chart adds that spend to the previous one so the line goes up.

Running Total Actual =

CALCULATE (

    SUM ( 'Tasks & Baselines'[TaskFixedCost] ),

    USERELATIONSHIP ( TimeSet[TimeByDay], 'Tasks & Baselines'[TaskFinishDate] ),

    FILTER (

        ALL ( TimeSet ),

        'TimeSet'[TimeByDay] <= MAX (TimeSet[TimeByDay])

    )

)

This works great when looking at my data as a whole as I get a full trend of my spend

example picture:

However, I would like to advance this further by having the measure reset the aggregation start point at the start of each year to £0 spend. This will give me a line chart which trends spend over multiple years which I can plot against a yearly fluctuating budget

So something like this:

How can I adapt my measure to achieve this?

Thanks for your help

Matt 

[Matt22365]

Perfect, thank you very much!!!!

Matt 

[johnt75 · Accepted solution]

Try

Running Total Actual =
VAR MaxDate =
    MAX ( TimeSet[TimeByDay] )
VAR StartOfCurrentYear =
    DATE ( YEAR ( MaxDate ), 1, 1 )
RETURN
    CALCULATE (
        SUM ( 'Tasks & Baselines'[TaskFixedCost] ),
        USERELATIONSHIP ( TimeSet[TimeByDay], 'Tasks & Baselines'[TaskFinishDate] ),
        DATESBETWEEN ( 'TimeSet'[TimeByDay], StartOfCurrentYear, MaxDate )
    )