Cumlative measure until max dat is reached

[RM117]

Hi all, I've got data in a table called actuals, and a table called Monthly Bud. Also have a Calendar table. I'm trying to do a cumlative total of the Budget table up to and including the latest date of the actuals. As of now I have actuals up to the end of January, soon to be refreshed to include the end of February. The current measure is working fine to the last date, it repeats the 1st date budget. this is the measure;

Measure3 =

var max_date = MAX(Actuals[Postg Date])

RETURN

CALCULATE(SUM('Monthly Bud'[Bud Mthly]),

DATESYTD('Calendar'[Date]),'Calendar'[Date] <= max_date)

What comes out of that is;

Month Year   Bud Mthly   Measure 3

Apr 2023        100              100

May 2023       100              200

Jun 2023        100               300

.

.

.

Dec 2023       100              900

Jan 2024        100              100

It may have something to do with Jan being a new year?

Any suggestions would be very much appreciated.

Thanks in advance;

Kevin

[JamesFR06 · Accepted solution]

HI

Please try this measure.

Budget Cumul =
VAR Period =
SELECTEDVALUE ( 'Date'[Année Mois] )
VAR MinDate =
DATE ( YEAR ( Period ), 1, 1 )
VAR MaxDate =
CALCULATE ( MAX ( 'Date'[Date] ), 'Date'[Année Mois] = Period )
RETURN
CALCULATE (
SUM ( Budget[Amount] ),
ALL ( 'Date' ),
'Date'[Date] >= MinDate
&& 'Date'[Date] <= MaxDate
)