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.

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

Sachy123 

Since PREVIOUSDAY is just shorthand for DATEADD you could change it to something like this.

Prior Day = 
VAR _Days = IF ( WEEKDAY ( SELECTEDVALUE ( Dates[Date] ) ) = 2, -3, -1)
RETURN
CALCULATE(
    [Sum],
    DATEADD(Dates[Date],_Days,DAY)
    )

On Mondays it shifts the date by -3 instead of -1 giving us Friday's amount.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi jdbuchanan71  Anonymous 

i am stuck with a similar sort of request  . 

The requirement is to for the users to select the no of days they would want to look back thier data from a selected date . 

E.g. if they select 10 days , then the report should show data for last 10 business days instead of calendar days . 

I have tried this 

 

previousd =
var selecteddate = SELECTEDVALUE('Date DATE'[EFFECTIVE_DATE])
var days = SELECTEDVALUE('Number of Days'[NoofDays]) --[ this is a parameter which is avaialbel for user to select the nof of days they want to look back from the selected date ]
Var lastday = DATE(YEAR(selecteddate),MONTH(selecteddate),DAY(selecteddate)- days)
var d = WEEKDAY(lastday,2)
var pwd =
IF ((d = 6),days+2 , IF((d = 7),days+3,days+1))
return pwd

 

this works for only 1 week, if user selects the no of days > 7 , then i would have to add 2 weekends days, which am unable to get this working . 

Would you suggest any easier way to write a DAX ?