running total per year

[Anonymous]

Hello Community,

I would appreciated your help withe the below.

I want to

• find the running total value
• create a graph its progress through all years

Especially when it comes to the graph, I want to be able to filter it by year or number of years, without impacting the running total calculation.

My source table is

Project	Year	Value
PR1	2020	100
PR1	2021	200
PR2	2019	50
PR2	2020	75
PR2	2021	100
PR3	2021	95
PR3	2022	105
PR3	2023	120
PR4	2023	100

I want to create this table.

Year	Aggregated Value
2019	50
2022	225
2021	620
2022	725
2023	945

Kind regards,

George

[AlB · Accepted solution]

Hi @Anonymous 

Best to use a date table instead. Try this:

Measure =
VAR maxYear_ =
    MAX ( Table1[Year] )
RETURN
    CALCULATE ( SUM ( Table1[Value] ), Table1[Year] <= maxYear_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@Anonymous 

In maxYear_ you get the current year in the filter context, the one in the current row of your visual. 

The Table1[Year] <= maxYear_ part then tells the CALCULATE to compute  SUM ( Table1[Value] ) over all the years that are equal or smaller than the current year.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

thank you @AlB , for me is always very helpful to also know the logic behind the solution, kind regards.

[AlB]

@Anonymous 

the solution above does not use a date table

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

measure = calculate(sum(Table[Value]), filter(allselected(Table), Table[Year] <=max(Table[Year])))

[Anonymous]

Thank you @amitchandak 

Your proposal worked just fine.

May I only ask to elaborate more on the logic behind your solution? 

I also follow your LinkedIn posts , extremely helpful !

Thank you

George

[AlB · Accepted solution]

Hi @Anonymous 

Best to use a date table instead. Try this:

Measure =
VAR maxYear_ =
    MAX ( Table1[Year] )
RETURN
    CALCULATE ( SUM ( Table1[Value] ), Table1[Year] <= maxYear_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you @AlB 

Your proposal worked out perfectly.

Can I only ask how the variable maxYear interacts with CALCULATE in order to produce the correct result?

Thank you,

George

[Anonymous]

Hello @AlB , is there a way to resolve this without using a date table? Thank you