Cummulative sum with reference date in another table

[cferraz_hemav]

Hi all!

I have a nightmare trying to sum the accumulated value

What I have:

Table 1

(...) | field_id | (...) | start_date |

Table 2

(...) | field_id | info_date | data | 

What I need is to have the accumulated_data (sum) from the start_date to the info_date:

Table 2

(...) | field_id | info_date | data | accumulated_data

Any idea to the easier/faster way to do it?

Thanks!

[v-alq-msft · Accepted solution]

Hi, @cferraz_hemav 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

There is no relationship between two tables. You may create a measure or a calculated column as below.

Measure:

Accumulated Measure = 
var _fieldid = SELECTEDVALUE(Table2[field_id])
var _infodate = SELECTEDVALUE(Table2[info_date])
var _startdate =
LOOKUPVALUE(Table1[start_date],Table1[field_id],_fieldid)
var result =
CALCULATE(
    SUM(Table2[data]),
    FILTER(
        ALL(Table2),
        Table2[field_id]=_fieldid&&
        Table2[info_date]>=_startdate&&
        Table2[info_date]<=_infodate
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Calculated column:

Accumulated Column = 
var _fieldid = Table2[field_id]
var _infodate = Table2[info_date]
var _startdate =
LOOKUPVALUE(Table1[start_date],Table1[field_id],_fieldid)
var result =
CALCULATE(
    SUM(Table2[data]),
    FILTER(
        ALL(Table2),
        Table2[field_id]=_fieldid&&
        Table2[info_date]>=_startdate&&
        Table2[info_date]<=_infodate
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cferraz_hemav]

Thank you very much for your time and fast reply @v-alq-msft !!

[amitchandak]

@cferraz_hemav , Can you share sample data and sample output in table format?

refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

for Cummulative  need example data

[cferraz_hemav]

Thanks for the fast reply!!

Table 1

field_id	start_date
31424	01/01/2020
00156	06/06/2020

Table 2 (in green the wanted one)

field_id	info_date	data	accumulated_data
31424	05/01/2020	-21,28	-21,28
31424	15/01/2020	-22,11	-43,39
00156	05/01/2020	-33,05	0

hope this example is enough