Forum Discussion

RM117's avatar
RM117
Regular Visitor
2 years ago
Solved

Cumlative measure until max dat is reached

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

"Need Help"
  • JamesFR06's avatar
    JamesFR06
    2 years ago

    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
    )

1 Reply

  • JamesFR06's avatar
    JamesFR06
    Icon for Resolver IV rankResolver IV
    2 years ago

    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
    )