Cumulative Column Sum between Two Dates

[rc8425]

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! 

[parry2k]

@rc8425 are you looking in date dimension on how many working days are in a month? CorrecT?

[rc8425 · Accepted solution]

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"

)

[parry2k]

@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 )