4 Replies Latest reply on Jun 15, 2016 5:53 AM by Sebastian Hopfensperger

    Create a new calculated date dimension based on two factors from two other separate date dimensions

    Sebastian Hopfensperger

      Hi All,


      I think this might be a complex one, and I have been trying to solve it for a while.


      From my data source I have two main date dimensions:

                - Order Date &

                - Delivery Date


      What I need to do it create a new date dimension (calculated field) that in effect changes/makes a new Order Date, depending on when the Order Date is throughout the Year as Well as the delivery Date.


      Note: Financial Year starts on October 1st.


      So, in more detail:


      In writing, the formula I need is:


      If the Order Date is in a previous FY year to the Delivery date (e.g. Ordered in FY2015 but Delivered in FY2016) then in need to create/calculate a new date field that changes the Order Date to the 1st October in the same FY as the Delivery Date. If the Order is in the same FY year as the Delivery Date, then the normal Order Date can be used.


      Any further questions/info please reply.


      Help will be greatly appreciated!