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

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.

Help will be greatly appreciated!

Thanks

• ###### 1. Re: Create a new calculated date dimension based on two factors from two other separate date dimensions

hi Sebastian,

Hopefully I've understood your requirements correctly, or at least the below/attached will get us on the right road!...btw I've also assumed that Order Date and Delivery date are in the same row for an order (if not it can be done but gets pretty complicated!)

So first I need to create a calculated field to allow us to get the FY (Financial Year) for both Order and Delivery dates...as you'll probably encountered although we can change the default FY start for a date in the Viz, when we use the YEAR([Order Date]) it gives the calendar year...so we use the following formulas

[Order FY Year]

[Delivery FY Year]

in the table in the attached you can see what it's doing...and as row level calculations there run 'off canvas' (i.e. doesn't matter what VizLoD we use)

Once we have these 2, the calculation you want is much easier...

[New Order Year (to Use)]

IF [Order FY Year]<[Delivery FY Year] THEN [Order FY Year]+1 ELSE [Order FY Year] END

Hope that solves your issue, and makes sense, but if not (on either score!) let me know.

1 of 1 people found this helpful
• ###### 2. Re: Create a new calculated date dimension based on two factors from two other separate date dimensions

Thanks for the quick response!

Yes the two dates are in the same row in the data source.

This work very well for creating a new FY year for the orders, So now all orders that were made in the FY before they were delivered are now showing the same FY as the delivery.

However, I need those orders that were made in the previous year to the delivery, to be given an actual date of the 1st October in the delivery FY...

Thanks

• ###### 3. Re: Create a new calculated date dimension based on two factors from two other separate date dimensions

...glad they are in the same row!!

So I think we can just adapt the formula a little to bring back a date....I've added a new formula

[New Order Date (to Use)]

IF [Order FY Year]<[Delivery FY Year] THEN DATE('01/10/'+STR([Order FY Year])) ELSE [Order Date] END

Which I think now does what you need.

1 of 1 people found this helpful
• ###### 4. Re: Create a new calculated date dimension based on two factors from two other separate date dimensions

Yes! This is doing it perfectly!

Thanks so much!