Forum Discussion
Helper VYear to date cumulative
Hi,
I am looking to build out a line chart which shows a cumulative year to date position.
My x-axis will be closed_date (month) with the legend being closed_date (year). My value is total_amount.
I want the line to start at £0 and not show any values post today's date.
Exanple data for 2026. I hope the format is ok.
closed_date | total_amount | cumulative_amount |
01/01/2026 | £0 | £0 |
02/01/2026 | £12,500 | £12,500 |
03/01/2026 | £14,200 | £26,700 |
04/01/2026 | £0 | £26,700 |
05/01/2026 | £0 | £26,700 |
06/01/2026 | £13,750 | £40,450 |
07/01/2026 | £11,900 | £52,350 |
08/01/2026 | £16,300 | £68,650 |
09/01/2026 | £15,200 | £83,850 |
10/01/2026 | £14,800 | £98,650 |
11/01/2026 | £0 | £98,650 |
12/01/2026 | £0 | £98,650 |
13/01/2026 | £17,400 | £116,050 |
14/01/2026 | £12,900 | £128,950 |
15/01/2026 | £18,200 | £147,150 |
16/01/2026 | £13,500 | £160,650 |
17/01/2026 | £15,300 | £175,950 |
18/01/2026 | £0 | £175,950 |
19/01/2026 | £0 | £175,950 |
20/01/2026 | £19,400 | £195,350 |
21/01/2026 | £11,700 | £207,050 |
22/01/2026 | £12,900 | £219,950 |
23/01/2026 | £17,600 | £237,550 |
24/01/2026 | £16,200 | £253,750 |
25/01/2026 | £0 | £253,750 |
26/01/2026 | £0 | £253,750 |
27/01/2026 | £18,900 | £272,650 |
28/01/2026 | £14,300 | £286,950 |
29/01/2026 | £15,700 | £302,650 |
30/01/2026 | £16,900 | £319,550 |
31/01/2026 | £17,200 | £336,750 |
01/02/2026 | £0 | £336,750 |
02/02/2026 | £0 | £336,750 |
03/02/2026 | £18,400 | £355,150 |
04/02/2026 | £14,900 | £370,050 |
05/02/2026 | £13,800 | £383,850 |
06/02/2026 | £19,600 | £403,450 |
07/02/2026 | £15,900 | £419,350 |
08/02/2026 | £0 | £419,350 |
09/02/2026 | £0 | £419,350 |
10/02/2026 | £17,800 | £437,150 |
11/02/2026 | £13,400 | £450,550 |
12/02/2026 | £12,700 | £463,250 |
13/02/2026 | £17,900 | £481,150 |
14/02/2026 | £18,500 | £499,650 |
15/02/2026 | £0 | £499,650 |
16/02/2026 | £0 | £499,650 |
17/02/2026 | £16,800 | £516,450 |
18/02/2026 | £14,200 | £530,650 |
19/02/2026 | £15,900 | £546,550 |
20/02/2026 | £18,700 | £565,250 |
21/02/2026 | £17,300 | £582,550 |
22/02/2026 | £0 | £582,550 |
23/02/2026 | £0 | £582,550 |
24/02/2026 | £19,800 | £602,350 |
25/02/2026 | £18,900 | £621,250 |
26/02/2026 | £16,600 | £637,850 |
Hi M_SBS_6
you need a calendar table made of dates (say you name the table Calendar), months etc to connect one to many to your fact tables. Date column in it must be a primary key (no duplicated nor blanks).
Your measures will then be
1
MaxFactDate = CALCULATE ( MAX ( Fact[Date] ), REMOVEFILTERS ( ) )2YTD =VAR _MaxFactDate = [MaxFactDate]RETURNCALCULATE([YourMeasure],CALCULATETABLE(DATESYTD('Calendar'[Date]),KEEPFILTERS( 'Calendar'[Date] <= _MaxFactDate )))If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your threadWant to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
1 Reply
- FBergamaschi
Super User
Hi M_SBS_6
you need a calendar table made of dates (say you name the table Calendar), months etc to connect one to many to your fact tables. Date column in it must be a primary key (no duplicated nor blanks).
Your measures will then be
1
MaxFactDate = CALCULATE ( MAX ( Fact[Date] ), REMOVEFILTERS ( ) )2YTD =VAR _MaxFactDate = [MaxFactDate]RETURNCALCULATE([YourMeasure],CALCULATETABLE(DATESYTD('Calendar'[Date]),KEEPFILTERS( 'Calendar'[Date] <= _MaxFactDate )))If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your threadWant to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI