CODEWITHSUNDEEP
powerbidaxpowerpivotdaxstudio
Arnold Souza
Arnold Souza

Reputation: 671

Accumulate values over years in DAX

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 example table

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?

Upvotes: 2

Views: 697

Answers (3)

davidebacci
davidebacci

Reputation: 30304

Is this what you want (New Column):

enter image description here

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
)

Upvotes: 1

Mik
Mik

Reputation: 2103

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 
    )

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

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.

Upvotes: 0

Related Questions