Forum Discussion

ga4f's avatar
ga4f
Regular Visitor
3 years ago

Creating cummulative sum in powerquery

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]...
Need Help
rubayatyasmin's avatar
rubayatyasmin
Community Champion
3 years ago

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.