Creating cummulative sum in powerquery

[ga4f]

Hello everybody,

the thing that i'm struggling is that i have 2 tables,

Table1

Table2

What i'm trying to do is:
If Table2[dt_nota] >= Table1[Start] and Table2[dt_nota] <= [End] then SUM(Table2[val_base]) else 0.

this code above is only example, i know that is out of syntaxe.

The final result should be the total value by period and HAVE to be in powerquery. I tryed inumerous ways but unsucessuful.

[rubayatyasmin]

Hi, @ga4f 

you could employ the SUMX function and utilize a filter within that. The process is as follows:

1. First, create a relationship between your two tables. This is done through Power BI's relationship view. Let's assume you've created a relationship between Table1 and Table2 on a column named DateID.

2. Second, create a calculated column in Table2 that checks whether dt_nota is within the Start and End dates from Table1. This is an example of how you might write this in DAX:

WithinPeriod =
VAR CurrentDate = Table2[dt_nota]
VAR RelatedStart = RELATED(Table1[Start])
VAR RelatedEnd = RELATED(Table1[End])
RETURN IF(CurrentDate >= RelatedStart && CurrentDate <= RelatedEnd, 1, 0)

Third, create a DAX measure to sum up wall_base when WithinPeriod is 1. Here's how:

TotalByPeriod =
SUMX(
FILTER(Table2, Table2[WithinPeriod] = 1),
Table2[wall_base]
)

Remember that DAX relies on the context provided by the visualization, slicers, etc. So, ensure you set those up to provide the correct context for this calculation.

Please modify these DAX expressions as necessary to fit your specific data structure and names. This is just a general example and may need to be adjusted based on the specifics of your use case.