Hi,
There must be fairly simple way to achieve this however I am looking for a way to calculate this logic in DAX for a requirement which basically needs adding of Weekend (Sat or Sun) values of QTY over to Monday…
I have a date column coming from date_dim and qty fact coming from On Prem SQL server(Direct Query)
Sample & Desired Data attached
data.xlsx (11.4 KB)
Thanks,
Archer
Hi Archer,
There is no better way than watch the following course.
@sam.mckay has done an amazing job.
Take care,
Mariusz
Hey @mno,
Thanks for guiding me through this video
It worked , the below tweak !
Delv Qty Non Weekend =
VAR WeekendCheck = SELECTEDVALUE(date_dimension[IsWeekend]) = "1"
VAR WorkdayCheck = SELECTEDVALUE(date_dimension[WeekDayName]) = "Monday"
VAR DeliveryQty = Sum(Orders[delv_Qty])
RETURN
IF(WeekendCheck = TRUE(),BLANK(),
IF(WorkdayCheck = FALSE(),
DeliveryQty,
CALCULATE(Sum(Orders[delv_Qty]),FILTER(ALL(date_dimension),
date_dimension[Date] > Min(date_dimension[Date]) - 3 && date_dimension[Date] <= MIN(date_dimension[Date])))))
Thanks
Archer
Hi @Archer,
That’s great!
You can upgrade this solution by including holidays.
Let’s say, holiday is on Monday than the volume is moved to Tuesday (if that’s your case of course).
You can find below my measure.
It’s long but simple (I had to figure out the correct total).
OL REN =
VAR WeekdayCheck = SELECTEDVALUE( Dates[isWD] ) = "1"
VAR ActualDate = SELECTEDVALUE( Dates[Date] )
VAR LastWD = CALCULATE(
MAX( Dates[Date] ),
FILTER( all( Dates ), Dates[Date] < ActualDate &&
Dates[isWD] = "1" )
)
VAR MinDateSelected = CALCULATE( MIN( Dates[Date] ), ALLSELECTED( Dates[Date] ) )
VAR FirstSD = CALCULATE(
MAX( Dates[Date] ),
FILTER( ALL( Dates ), Dates[Date] < MinDateSelected &&
Dates[isWD] = "1" )
)
VAR LastSD = CALCULATE( MAX( Dates[Date] ), ALLSELECTED( Dates[Date] ), Dates[isWD] = "1" )
VAR Result = IF(
WeekdayCheck = FALSE(),
BLANK(),
CALCULATE(
[OL RE],
FILTER(
ALL( Dates[Date] ),
Dates[Date] > LastWD &&
Dates[Date] <= MIN( Dates[Date] )
)
)
)
VAR Result2 = CALCULATE(
[OL RE],
FILTER( ALL( Dates ), Dates[Date] > FirstSD &&
Dates[Date] <= LastSD )
)
RETURN
IF( ISINSCOPE( Dates[Date] ), Result, Result2 )
Take care,
Mariusz
Well done, great formula and support on this thread
Hi @mno,
Thanks for your extended solution and accurate guidance on my initial requirement.
I would need some direction on Additional logic on retrieving previous value.
Requirement is to exclude Weekends and bring Friday Value over to Monday as Prev Value so I used the below DAX which is working fine as expected.
Prev Val2 =
VAR SelDate = SELECTEDVALUE( Dates[Date] )
VAR Weekend = SELECTEDVALUE(Dates[IsWeekend]) = "True"
VAR Workday = SELECTEDVALUE(Dates[WeekDayName]) = "Monday"
VAR PrevDate = IF(Weekend = TRUE(),Blank(),
If(Workday = FALSE(),
CALCULATE(
MAX( Dates[Date] ),
FILTER(
ALL(Dates[Date] ),
Dates[Date] < SelDate
)),
CALCULATE(
MAX( Dates[Date] ),
FILTER(
ALL(Dates[Date] ),
Dates[Date] < SelDate - 2 ))))
How can I exclude Holiday’s as well ? I have IsHoliday Flag True/False coming from my Dates time dimension table so whenever there is a holiday it is giving blank prev value whereas I need to retrieve the previous working day value
data.xlsx (12.1 KB)
Please suggest or guide me here
Thanks
Archer
Hi @Archer,
Let me try to explain how holidays may be included 
The key part is the following variable, which returns the last working day earlier than actual date…
VAR LastWD =
CALCULATE(
MAX( Dates[Date] ),
FILTER( all( Dates ), Dates[Date] < ActualDate &&
Dates[isWD] = "1" )
)
Actual date is another variable, this one returns the selected date:
VAR ActualDate =
SELECTEDVALUE( Dates[Date] )
The final calculation includes the LastWD which is the key.
VAR Result =
IF(
WeekdayCheck = FALSE(),
BLANK(),
CALCULATE(
[OL RE],
FILTER(
ALL( Dates[Date] ),
Dates[Date] > LastWD &&
Dates[Date] <= MIN( Dates[Date] )
)
)
)
Hope it’s clear.
If not, could you please share your pbix file?
Take care,
Mariusz
Thank you loads for this !