Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Matt22365
Resolver III
Resolver III

Reset aggregation measure at the start of each year

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:

Matt22365_0-1670513319018.png

 

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:

Matt22365_1-1670513349109.png

 

How can I adapt my measure to achieve this?

 

Thanks for your help

 

Matt 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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 )
    )

View solution in original post

2 REPLIES 2
Matt22365
Resolver III
Resolver III

Perfect, thank you very much!!!!

 

Matt 

johnt75
Super User
Super User

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 )
    )

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.