Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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!
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
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!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |