Display last friday date compared to any day in a current week

[Prashant S A]

Hi,

How can we display last friday date compared to any day in a current week?

Edited June 14 by Prashant S A

[Anthony Alvarez]

If you would like to show the date of the last Friday on the screen, you have a few options:

• Using a Text Area + Calculated Value
• The "Display Value" Mod
• You could load the date of the last Friday into a Document Property and show the date in chart titles, Text Areas, etc.
• You could use a calculated column and process a tables worth of dates and Friday offset dates

Here are a few functions that can help you calculate the date of the most recent Friday:

• Today() - This will provide today's date
• DayOfWeek() - This will convert any date into the day of week number (Saturday = 6, Friday = 5, Thursday = 4, ..., Sunday = 0)
• A custom expression like the one that follows could calculate the Days Since Last Friday
  • Case DayOfWeek(Today())
        when 6 then -1 //Saturday
        when 5 then 0 //Friday
        when 4 then -6 //Thursday
        when 3 then -5 //Wednesday
        when 2 then -4 //Tuesday
        when 1 then -3 //Monday
        when 0 then -2 //Sunday
        else null
      end
  • In this custom expression example if the input date is a Friday, it returns the same Friday's date.
    • If you would like to return the previous Friday's date, swap the like  "when 5 then 0 //Friday" with  "when 5 then -7 //Friday"
• One final custom expression calculates the date of last Friday:
  • DateAdd('day',
    Case DayOfWeek(Today()) 
        when 6 then -1 
        when 5 then 0 
        when 4 then -6 
        when 3 then -5 
        when 2 then -4 
        when 1 then -3 
        when 0 then -2
        else null
      end,
      Today()
    )
  • This custom expression uses the DateAdd('day', #DaysSinceFriday, InputDate) format
• Feel free to swap the Today() function with a document property or a column input (if you end up using a calculated column in a data table)

 

One last thought, you could also build a Data Function (Expression Function) to contain the same logic as above. This approach would be a clean way to tackle this problem.

 

[Jose Leviaguirre]

Here are some more date expression functions
 

Today	[Date]=Date(DateTimeNow())
Last 30 Days	[Date] >= dateadd('dd',-30,DateTimeNow())
This Week	Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow())
This Month	Month([Date]) = month(DateTimeNow()) and Year([Date]) = year(DateTimeNow())
Year to Date	Year([Date]) = Year(DateTimeNow())
Last Month
Last 12 Months	Month([Date]) = Month(dateadd('mm',-1,DateTimeNow())) and Year([Date]) = Year(dateadd('mm',-1,DateTimeNow()))
[Date] >= dateadd('mm',-12,DateTimeNow())
Q1	Quarter([Date]) = 1 and Year([Date]) = Year(DateTimeNow())
Q2	Quarter([Date]) = 2 and Year([Date]) = Year(DateTimeNow())
Q3	Quarter([Date]) = 3 and Year([Date]) = Year(DateTimeNow())
Q4	Quarter([Date]) = 4 and Year([Date]) = Year(DateTimeNow())
Begining of Month	Date(Year(DateTimeNow()),Month(DateTimeNow()),1)
End of Month	DateAdd('day',-1,date(Year(DateTimeNow()),Month(DateAdd('month',1,DateTimeNow())),1))
End of Last Month	DateAdd("day",-1,date(Year(DateTimeNow()),Month(DateTimeNow()),1))
End of Next Month	DateAdd("day",-1,date(Year(DateTimeNow()),Month(DateAdd("month",2,DateTimeNow())),1))
First Weekending of the year	Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)), DATE(year(DateTimeNow()),1,1))
Last Weekending (last saturday)	DateAdd("week",Week(DateTimeNow()) - 2,Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))
End of this week	DateAdd("week",Week(DateTimeNow()) - 1,Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1)))
End of next week	Date(DateAdd("week",Week(DateTimeNow()),Dateadd("day",6 - DayOfWeek(DATE(Year(DateTimeNow()),1,1)),DATE(year(DateTimeNow()),1,1))))
Previous Quarter end date	DateAdd("day",-1,DateAdd("quarter",Quarter(DateTimeNow()) - 1,Date(Year(DateTimeNow()),1,1)))

 

[Prashant S A]

Thanks Anthony, Jose👍