DAX: How to calculate the sum of a column/rows for the following week

[ironboy0423]

Hello Community, I have a power bi dax that calculates the hours based on selected start of week. I am currently getting the correct value for week 1, however, week 2 or the following week returns the total sum of entire rows. I select the start of week using a slicer. Below is the dax I am currently using for week 1 and week 2:

Week1Duration = VAR SelectedWeekStart = SELECTEDVALUE('01_Date'[Start of Week]) VAR Week1StartDate = CALCULATE(MIN('01_Date'[CalendarDate]), '01_Date'[Start of Week] = SelectedWeekStart) VAR Week1EndDate = Week1StartDate + 6 RETURN CALCULATE( SUM(FactTable[Duration]), 'FactTable'[Date] >= Week1StartDate && 'FactTable'[Date] <= Week1EndDate )

Week2Duration = CALCULATE(SUM('FactTable'[Duration]), REMOVEFILTERS('01_Date'[Start of Week]), '01_Date'[Week ID] = MAX('01_Date'[Week ID]) + 1)

[ironboy0423]

Hi @Anonymous yes it is, this is the current result I am getting so far:

[tamerj1]

Hi @ironboy0423 
Are the date table and the fact table connected? How does your visual look like?

[ironboy0423 · Accepted solution]

It works when I organize the relationship between tables, thank you

[ironboy0423]

And to add to this, yes it is connected but not active, when I activate the relationship, it will introduce ambuigity to other fact table that is currently connected to date table