Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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 , Can you share sample data and sample output in table format?
refer if this blog can help
for Cummulative need example data
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |