Forum Discussion

rc8425's avatar
rc8425
Regular Visitor
7 years ago
Solved

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. ...
Need Help
  • rc8425's avatar
    rc8425
    7 years ago

    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's avatar
rc8425
Regular Visitor
7 years ago

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's avatar
parry2k
Super User
7 years ago

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 )