Cumulative Amount sum Actual + Forecast based on a date

[dpombal]

Hi, all I have a dax question regarding following calculation

I have 1 actual transactions table

customer_id  amount_actual  date_transaction_actual

1	10	01/01/2016
1	20	01/01/2017
2	50	01/04/2016
2	40	01/05/2017
2	60	01/08/2017

2. ) And an identical forecast transactions table

customer_id amount_forecast date_transaction_forecast

1	5	01/04/2017
1	15	01/01/2018
2	35	01/03/2017
2	25	01/05/2018

3. ) And a Forecast_updates table with a line for each customer

customer_id  forecast_update

1	01/01/2018
2	31/12/2017

I need to calculate a cumulative sums from fist date until forecast date from sum amount of actual table and after forecast update date , accumulate to prior amount the amount transactions from forecast table.

Example for customer 1

DATA ACTUAL

1 January 2016 --> 10 ---> Accum (10)

1 January 2017 --> 20 ---> Accum (10+20=30)

DATA FORECAST

1 April 2017 --> 5 ---> Do not use becasue 1 April 2017 is < Forecast update for customer 1 , (01/01/2018)

1 January 2018 --> 15 ---> Accum (10+20+15 =45)

// initial measures

actual_amount = SUM(actual_transactions[amount_actual])
forecast_amount = SUM(forecast_transactions[amount_forecast])



// cumulative all

cumulative_amount_actual = CALCULATE([actual_amount];FILTER(ALL('Calendar'[Date]);'Calendar'[Date] <= MAX('Calendar'[Date])))

cumulative_amount_forecast = CALCULATE([amount_forecast];FILTER(ALL('Calendar'[Date]);'Calendar'[Date] <= MAX('Calendar'[Date])))

// chances using a custom date should be changed by  table date

cumulative_amount_mixed_v2 = IF(MAX('Calendar'[Date]) < DATE(2018;1;1)
                              ;CALCULATE([actual_amount];FILTER(ALL('Calendar'[Date]);'Calendar'[Date] < DATE(2018;1;1) ))
                              ;CALCULATE([forecast_amount];FILTER(ALL('Calendar'[Date]);'Calendar'[Date] >= DATE(2018;1;1) ))
                              )

Link to powerbi pbix file here https://1drv.ms/u/s!Am7buNMZi-gwkCu4GDiY1wXwWU8w

[v-chuncz-msft]

@dpombal,

You may refer to the measure below.

Measure =
VAR d =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR d2 =
    SELECTEDVALUE ( Forecast_Updates[forecast_update] )
VAR t =
    UNION (
        ALLSELECTED ( actual_transactions ),
        CALCULATETABLE (
            ALLSELECTED ( forecast_transactions ),
            forecast_transactions[date_transaction_forecast] >= d2
        )
    )
RETURN
    SUMX (
        FILTER ( t, actual_transactions[date_transaction_actual] <= d ),
        actual_transactions[amount_actual]
    )

[dpombal]

Hi @v-chuncz-msft  this is a quite good solution however it only works when a customer is selected

Selecting customer 1  Works perfect--> 45 is final amount

Selecting customer2  Works perfect 175 is final amount

Without selections, the behaviour should be the same (45 +175 = 220) Correct and not total amount (260)

Updated report here

https://1drv.ms/u/s!Am7buNMZi-gwkCu4GDiY1wXwWU8w

Regards

[v-chuncz-msft · Accepted solution]

@dpombal,

Change it as follows.

Measure =
VAR d =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR t =
    UNION (
        ALLSELECTED ( actual_transactions ),
        FILTER (
            ALLSELECTED ( forecast_transactions ),
            forecast_transactions[date_transaction_forecast]
                >= RELATED ( Forecast_Updates[forecast_update] )
        )
    )
RETURN
    SUMX (
        FILTER ( t, actual_transactions[date_transaction_actual] <= d ),
        actual_transactions[amount_actual]
    )

[dpombal]

Hi friend, just one final question, if tables haven't same number of fields and in in different positions how do you adapt the union statement,  Selectcolumns should be used?

[v-chuncz-msft]

@dpombal,

Yes, you got it.

[dpombal]

thanks will try this solution looks complex, let me understand it, thanks in advance

[dpombal]