14 Replies Latest reply on May 17, 2018 2:57 AM by Anupam Mehta

    Previous three business days calculation

    raoul.tller

      Hi,

       

      Currently I am trying to write a calculation that works with my Data selection parameter, in the parameter the user will select one date and it will show the previous three business days of that particular date.

      In the date dimension I am using there is a field called Business_Day which is eather Y or N (yes, no), I am trying to combine this with DATENAME(weekday). Currently I have this:

       

      IF DATENAME('weekday', [Date selection])= 'Monday' AND [business_day] = 'N'

      THEN [Date] = DATEADD('weekday', -3, [Date selection])

      OR [Date] = DATEADD('weekday', -4, [Date selection])

      OR [Date] = DATEADD('weekday', -5, [Date selection])

       

      ELSEIF DATENAME('weekday', [Date selection])= 'Monday' AND [business_day] = 'Y'

      THEN [Date] = [Date selection]

      OR [Date] = DATEADD('weekday', -3, [Date selection])

      OR [Date] = DATEADD('weekday', -4, [Date selection])

       

      ELSEIF DATENAME('weekday', [Date selection])!= 'Monday'

      THEN [Date] = [Date selection]

      OR [Date] = DATEADD('weekday', -1, [Date selection])

      OR [Date] = DATEADD('weekday', -2, [Date selection])

      END

       

       

      This kind of works, except I want to check everyday on Business_Day = Y so I have to expand this calculation, but I cant seem to figure out how, is it possible to somehow get the DATENAME of the DATEADD, -1, -2, -3 ETC? For example if I select Easter monday (2-4-2018) it currently shows me 30-3-2018 and 29-3-2018. But 30-3-2018 is Business_Day = N (good friday), so in this perfect example I want to show 29-3, 28-3, 27-3.

       

      Or is it possible to get this in another way, I saw some suggestions for LoD expressions in other topics, which might make it easier.