Forum Discussion

Sachy123's avatar
Sachy123
Icon for Helper V rankHelper V
5 years ago
Solved

Previous Day function (only weekdays)

So, my data looks as below, Date column is sorted. and weekends are excluded. The measure is not taking previous day data..  And the problem occurs only on " Mondays" ! How can I ensure that on Mon...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Sachy123 , 

    I think what you need to judge is the weekday of date. If it is Monday ,you shifts the date by -3;Other date of weekdays ,shifts the date by -1.

    Previous Business Price =

    VAR BackDays= If ( WEEKDAY ( SELECTEDVALUE ( BusinessDayCalendar[Date] ),2 ) = 1, -3, -1)

    RETURN

    CALCULATE(SELECTEDVALUE(BusinessDayCalendar[Current Price]),DATEADD(BusinessDayCalendar[Date],BackDays,DAY))

      

    WEEKDAY ( SELECTEDVALUE ( BusinessDayCalendar[Date] ),2 ) = 1   is mean that the return value of Monday is 1 .

    Then you can judge if return value=1,will shift by -3;Otherwise ,shift by -1.

     

    The effect is as shown:

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sachy123's avatar
Sachy123
Icon for Helper V rankHelper V
5 years ago
PriorDay Price = 
VAR _WeekDay = WEEKDAY(DATEADD ( BusinessDayCalendar[Date].[Date],-1,DAY),2) 
VAR _BackDays= If (_WeekDay = 7,-3,-1)
RETURN
CALCULATE(
    [Current Price],
    DATEADD(BusinessDayCalendar[Date].[Date],_BackDays,DAY)
    )

 

I am not sure why is this not working.. 😕  this gives me the current price.. 😞

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
5 years ago

You don't need to calculate the previous weekday, you just need to adjust if the weekday of the date you are on is monday. It also looks like your 'BusinessDayCalendar' table may not be marked as the calendar table for the model?

What do you get when you use this.

PriorDay Price = 
VAR _BackDays= If ( WEEKDAY ( SELECTEDVALUE ( BusinessDayCalendar[Date] ) ) = 2, -3, -1)
RETURN
CALCULATE(
    [Current Price],
    DATEADD(BusinessDayCalendar[Date],_BackDays,DAY)
    )