1 Reply Latest reply on Dec 6, 2017 5:18 PM by Shinichiro Murakami

    Using fiscal dates for "same day" last year sales

    Milena Khalil

      Hi,

       

      I have a current dashboard set up where the user selects a particular date (eg. November 1, 2017), and the worksheet shows sales for the same day one year ago, ie November 1, 2016.

      There is a new requirement where when the user selects November 1, 2017 (Wednesday, Day 305), the worksheet display sales for the same fiscal day one year ago (Wednesday, Day 305, November 2, 2016).

       

      I've attached a sample workbook (v10.0) with similar data as my actual workbook. There are dimensions that indicate the Fiscal day number of a certain date, as well as its fiscal year and the day of the week.

      Note that Calendar year =! Fiscal year (eg. January 1, 2016 has a calendar year of 2016 but a fiscal year of 2015).

       

      My ideas so far are:

      1. if the difference between fiscal years is 1 and the difference between the values of fiscal days is 0 then show sales

      2. if the fiscal year is less than or equal to the year datepart of the selected date, and the fiscal day equals the fiscal day of the selected date then show sales

       

      I just don't know how to translate this into syntax that makes sense in Tableau

       

      Thanks in advance!