Calculate cumulative sum from previous workday to current workday

[Anonymous]

I have a data model with a fact table of incoming orders and a date table with a day type column - W for workday, E for weekend, H for holiday. I would like to calculate a running total that sums orders from the day after the last workday until the current workday as illustrated below:

So any day with a workday preceeding it would ony sum 1 number, but the days with weekends or holidays preceeding would sum all the prior days that aren't workdays.

I was trying to use datesbetween and previousday where day type = W, but I am not getting anywhere. Does anyone have any suggestions? 

[v-juanli-msft]

Hi @Anonymous

I try it on my site with this formula 

Column =
VAR previousProd =
    CALCULATE (
        LASTNONBLANK ( Sheet2[PROD], 1 ),
        FILTER ( Sheet2, Sheet2[CALENAR_DATE] = EARLIER ( Sheet2[CALENAR_DATE] ) - 1 )
    )
VAR flag1 =
    IF (
        Sheet2[PROD] <> Sheet2[previousProd]
            && Sheet2[previousProd] <> BLANK ()
            || Sheet2[previousProd] = "E",
        0,
        1
    )
VAR flag2 =
    CALCULATE (
        SUM ( Sheet2[flag1] ),
        FILTER ( Sheet2, Sheet2[CALENAR_DATE] <= EARLIER ( Sheet2[CALENAR_DATE] ) )
    )
VAR flag3 =
    CALCULATE (
        SUM ( Sheet2[orders] ),
        FILTER (
            Sheet2,
            Sheet2[flag1] = 0
                && Sheet2[flag2] = EARLIER ( Sheet2[flag2] )
        )
    )
RETURN
    IF (
        Sheet2[PROD] = "W"
            && Sheet2[flag1] = 1,
        Sheet2[orders],
        IF ( Sheet2[PROD] = "E" || Sheet2[PROD] = "H", BLANK (), Sheet2[flag3] )
    )

finally it works out

Best regards

Maggie

[Anonymous · Accepted solution]

Thank you Maggie - I tried your function, but I was not able to get it to work. I am fairly new to DAX, and so I am not sure I replaced your columns with the correct ones from my query. I did, however, figure out a solution that works for me:

Incoming Dollars =
CALCULATE (
    [Total Dollars],
    FILTER (
        ALL ( dimBICCalendar ),
        AND (
            dimBICCalendar[CALENDAR_DATE] <= MAX ( tblIncomingOrders[CALENDAR_DATE] ),
            dimBICCalendar[CALENDAR_DATE] > MAX ( dimBICCalendar[PREV_WORKDAY] )
        )
    )
)

I calculate a previous workday column in my table in the SQL query. Total dollars is a measure that sums the dollars in my dataset (orders in my example). I created this as a measure as well, not a calculated column. Thanks again!