Accumulate values over years in DAX

Question

My table has a column that calculates the cumulative total year-to-date (YTD). The column is Balance BRL. It works ok using the DAX function DATESYTD.

The column Balance BRL is perfectly achieved using the DAX below:

Balance BRL = 
CALCULATE (
    'Fact_balance_cash'[realized_application],
    DATESYTD('Calendar'[Date])
)
+
CALCULATE (
    'Fact_balance_cash'[realized_investiments],
    DATESYTD('Calendar'[Date])
)
+
CALCULATE (
    'Fact_balance_cash'[realized_balance],
    DATESYTD ('Calendar'[Date])
 )
+
CALCULATE (
    [Forecast_R],
    DATESYTD ( 'Calendar'[Date] )
)

I was asked to continue to accumulate the values over 2023. That is not compatible with the formula DATESYTD.

I read this article from daxpatterns.com: Link Cumulative Total

I tried to implement it. the result is -R$ 148.017.749,0527 which is indeed the final balance. But it seems the date does not take effect to properly calculate it by month.

My current measure is the one below. I am fixing the date period until the end of January in 2023 just to test:

Balance BRL = 
    var period_begin = DATE(2022,1,1)
    var period_end = DATE(2023,1,31)

return

CALCULATE (
    'Fact_balance_cash'[realized_application],
    'Calendar'[Date]<=period_end
)
+
CALCULATE (
    'Fact_balance_cash'[realized_investiments],
    'Calendar'[Date]<=period_end
)
+
CALCULATE (
    'Fact_balance_cash'[realized_balance],
    'Calendar'[Date]<=period_end
 )
+
CALCULATE (
    [Forecast_R],
    'Calendar'[Date]<=period_end
)

How to solve this cumulative total that continues to calculate from 2022 until 2023?

Here you are: [Link to the PBIX](https://drive.google.com/file/d/1oqQjmeJSKOtIbXHM1OgobRVWlvGVU8no/view?usp=share_link) — Arnold Souza, Dec 29 '22 at 13:09

score 1 · Accepted Answer · answered Dec 29 '22 at 13:32

Is this what you want (New Column):

New = 

var period_end = MAX('Calendar'[Date])

return

CALCULATE (
    'Fact_balance_cash'[__auto_application_actutal],
    'Calendar'[Date]<=period_end
)
+
CALCULATE (
    'Fact_balance_cash'[__investment_actual],
    'Calendar'[Date]<=period_end
)
+
CALCULATE (
    'Fact_balance_cash'[__balance_actual],
    'Calendar'[Date]<=period_end
 )
+
CALCULATE (
    [__Forecast_R],
    'Calendar'[Date]<=period_end
)

score 0 · Answer 2 · answered Dec 29 '22 at 03:10

0

Can you try something like-

CALCULATE (
    SUM('Fact_balance_cash'[realized_application]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]>=period_begin
            && 'Calendar'[Date]<=period_end
    )
    
)

There is also a function like DATESBETWEEN you can check.

answered Dec 29 '22 at 03:10

mkRabbani

16,295
2
15
24

It is resulting in -148 milions BRL. Whitch is the result I extpect, but it is repeating all rows like the previou measure I did. [Here is the image](https://drive.google.com/file/d/1Z7CC2z4KP53tURhfmCkEtHhrP6CvvwMj/view?usp=sharing). [Here is the link to the PBIX - Measure __Total actual test 2](https://drive.google.com/file/d/11lU47SoMloBP_7jOayDJsXMuW_iWUSHQ/view?usp=sharing) – Arnold Souza Dec 29 '22 at 13:18

Mik · Answer 3 · 2022-12-29T11:28:16.397

0

VAR startDate = 
    CALCULATE(
       MIN(Calendar[Date])
      ,AllSELECTED(Calendar)
    )
VAR endDate = MAX(Calendar[Date])

RETURN
    CALCULATE(
           [realized_application] 
         + [realized_investiments] 
         + [realized_balance] 
         + [Forecast_R]
         ,'Calendar'[Date]>=startDate  && 'Calendar'[Date]<=endDate 
    )

edited Dec 29 '22 at 11:28

answered Dec 29 '22 at 10:38

Mik

2,099
1
5
19

Thank you for your help. Unfortunately the result is not the one expected. [Here is the image](https://drive.google.com/file/d/1mYXyBJo5cJwQmO79rJfnjiBc300oluoq/view?usp=sharing). [Here is the link to the PBIX - Measure __Total actual test 2](https://drive.google.com/file/d/1oqQjmeJSKOtIbXHM1OgobRVWlvGVU8no/view?usp=share_link). The result was supposed to be - 148,46 Millions BRL by the end of January/23 – Arnold Souza Dec 29 '22 at 13:06
can you add 2 measures with start and end dates ? – Mik Dec 29 '22 at 13:11
`MAX(Calendar[Date])` - fist measure to matrix – Mik Dec 29 '22 at 13:11
`CALCULATE( MIN(Calendar[Date]) ,AllSELECTED(Calendar) )` - second measure to matrix – Mik Dec 29 '22 at 13:12
then test each measure first `[realized_application]` with the same code just remove other 3, then only with `[realized_investiments]` and so on – Mik Dec 29 '22 at 13:13
Just to check what is wrong period start end dates or measures results. – Mik Dec 29 '22 at 13:13
Add measure codes as well – Mik Dec 29 '22 at 13:14
I'm going to my vacation, so I will not be able to reach the result. Try all steps and you will find the source of the problem. It will help you to find a solution. I think, that my measure is ok. The problem can come from data types, links, measures, filtering etc. So, if you find my answer usefull then you can mark the answer or upvote. I'll be back to SO only in the end of Jan. So, good lack and Happy new year!!! – Mik Dec 29 '22 at 13:26

Accumulate values over years in DAX

3 Answers3