Cumulative realise N-1 depending realise N

[alsolok]

Hello everyone,
I have a financial fact table which contains the columns accounting_date, CC, realized_amount.
I calculated the amount realized by doing sum(finance[amount_realise]) and an amount realized N-1 by doing CALCULATE(SUM(finance[amount_realise]),SAMEPERIODLASTYEAR('Dim Date'[Date]))
I can decline the amount realized and the amount realized n-1 per year, month and CC.
I remind you, we have a date dimension which is linked to the finance fact table through the accounting_date and date column of the date dimension.
An example: when I put the amount realized by CC in a table, the amount realized is indicated up to a given month and the amount realized N-1 over the whole year (which I find normal) Now I would like to calculate an cumulative amount N-1 depending on the amount realized, that is to say if for example for the year 2024 we have the amount realized in the finance table which is entered until June, I would also like to have a measurement amount realized N- 1 which will be the cumulative amount of the previous year until the month where we have the realized amount entered or when we select the month filter example April that realized amount N-1 is accumulated until the selected month.

Thank you all for your responses

[MFelix · Accepted solution]

Hi @alsolok ,

Check if this blog post and video can help you:

https://www.sqlbi.com/articles/previous-year-up-to-a-certain-date/

[alsolok]

Thank you

[bhanu_gautam]

@alsolok , For this requirement make sure you have one date table , then create measures 

AmountRealized = SUM(finance[realized_amount])

AmountRealizedN1 =
CALCULATE(
SUM(finance[realized_amount]),
SAMEPERIODLASTYEAR('Dim Date'[Date])
)

CumulativeAmountRealizedN1 =
VAR CurrentYear = MAX('Dim Date'[Year])
VAR CurrentMonth = MAX('Dim Date'[Month])
RETURN
CALCULATE(
[AmountRealizedN1],
FILTER(
ALL('Dim Date'),
'Dim Date'[Year] = CurrentYear - 1 &&
'Dim Date'[Month] <= CurrentMonth
)
)