Forum Discussion
Cumulative Column Sum between Two Dates
Hello Everyone,
This has GOT to be simple and I'm just overthinking it. I am trying to write an expression that returns which work day (non-weekend, non-holiday) a particular date is of a month. I have a Date column, a WorkDayCount column where a 1 represents a non-weekend day/non-holiday, and a 0 denotes a weekend day or holiday. I have a FirstDayofMonth column and a LastDayofMonth column. I want to create a fifth column that indicates the work day of that particular month by performing a cumulative sum of WorkDayCount between the end points of FirstDayofMonth and LastDayofMonth.
Any help is much appreciated!
Correct.
After rooting around in the forum, I was able to answer my own question using COUNTROWS filtering on those rows I identified as a non-weekend day/non-holiday with a 1
WorkDay =
if(
DateTable[WorkdayCount]=0,
blank(),
calculate(countrows(DateTable),
DATESBETWEEN(DateTable[Date],
STARTOFMONTH(DateTable[Date]),
DateTable[Date]),
DateTable[WorkdayCount]=1,
all(DateTable))
&"WD"
)
3 Replies
Correct.
After rooting around in the forum, I was able to answer my own question using COUNTROWS filtering on those rows I identified as a non-weekend day/non-holiday with a 1
WorkDay =
if(
DateTable[WorkdayCount]=0,
blank(),
calculate(countrows(DateTable),
DATESBETWEEN(DateTable[Date],
STARTOFMONTH(DateTable[Date]),
DateTable[Date]),
DateTable[WorkdayCount]=1,
all(DateTable))
&"WD"
)
rc8425 ok then you can add new column with following DAX, change column and table name as per your data model
Month Work Day = VAR __firstDate = CALCULATE( MAX( Workday[First] ) ) VAR __lastDaste = CALCULATE( MAX( Workday[Last] ) ) RETURN CALCULATE( SUM( Workday[Workday] ), Workday[First] >= __firstDate, Workday[Last] <= __lastDaste )
