Add Values from Weekend to Weekday

DAX DAX Calculations
1

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

2

Hi Archer,

There is no better way than watch the following course.
@sam.mckay has done an amazing job.

Take care,
Mariusz

1 Like
3

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

1 Like
4

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

1 Like
5

Well done, great formula and support on this thread

1 Like
6

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

7

Hi @Archer,

Let me try to explain how holidays may be included :wink:
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

8

@mno, You should know that you’re awesome :grinning: Thank you loads for this !

2 Likes
9

@Archer, You are always welcome :slight_smile: