CODEWITHSUNDEEP
powerbidax
jislesplr
jislesplr

Reputation: 37

Power BI DAX: How to calculate the sum of a row for distinct date only

I am currently updating this dax to make sure that I am getting the correct value from table2durationhours column - this is a calculated column from another table. However, when I use this dax it sum up all the rows from table2durationhours resulting to very large hours for 1 date. For example, there are multiple rows for 06/05/2022 and it sum up all of these, and it should be for 1 row only. Please see below dax I am currently using:

Week 1:

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[Table2DurationHours]), 'FactTable'[Date] >= Week1StartDate && 'FactTable'[Date] <= Week1EndDate )

Week 2:

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

Sample Table:

enter image description here

Upvotes: 0

Views: 199

Answers (1)

Pieter
Pieter

Reputation: 724

You're trying to sum the hours worked by week? (I'm not completely clear, but reading between the lines, that's what it appears.) The easiest way to do this is to create a calendar table... you can use code like this:

DimDate =

ADDCOLUMNS( CALENDAR( MINX('WorkData',[Date]), MAXX('WorkData',[Date]) ), "Year",YEAR([Date]), "MonthNo",MONTH([Date]), "Month",FORMAT([Date],"MMM"), "Weekday",FORMAT([Date],"DDD"), "DayOfWeek",WEEKDAY([Date]), "WeekOfYear",WEEKNUM([Date]), "Day",DAY([Date]))

Then you can do something simple in a matrix. Put "WeekOfYear" on ROWS and "DurationHours" on VALUES, and you'll get a sum by default of the total hours by weeknumber. I think that's what you were asking - if not, please clarify.

Upvotes: 0

Related Questions