Forum Discussion
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 Monday , data from Friday is taken as a previous day?
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.
5 Replies
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.
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 pwdthis 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 ?
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.. 😞
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) )
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.
