Find week (custom time dimension) from date in import
I'm working on a model which has a month format in my time dimension, this is nessecary because of workspace restrictions. But there is a need to add FTE using a begin and end week, so I've constructed a custom time dimension which looks like this:
The weeks are genererated and are given a begin and end date in the same proces.
In my import file I have transactional data, so I have a date on which a certain amount of hours are written. Looking like this:
I would like to connect the date to a week using my beginning and end-date connected to my weeks. So the first record has the date 15-02-2017, which I would like to connect to week 7 2017.
I tried several things, but I just can't seem to get the correct formula(s). I hope someone can help my along.
Thanks in advance for your help.
René den Uijl
This solution works but I would suggest to upgrade it - automate the formula in the new module's line item instead of values (Day Mapping.Timescale):
1) Create new line item inside weeks' list, let's name it as "Week Item" and put the formula: =Item(weeks' list)
2) And finally put the formula inside days' modules' line item (inside Day Mapping.Timescale): =IF ISBLANK(Weeks' List.Week Item[FIRSTNONBLANK: Weeks' List.Start]) THEN PREVIOUS(Timescale) ELSE Weeks.Week Item[FIRSTNONBLANK: Weeks' List.Start]
So you get automated solution
The simplest option is to create a mapping module (by day) that maps day to your timescale, then you can use this module in your transactional module to map to a line item formatted as your timescale list using a lookup
In the examples below, the timescale line items are formatted to my "fake timescale" list
Hope this helps
Hi Ansar and David,
Thank you both for the solution, it's working perfectly.
I thought I was almost there, but I just couldn't figure out the last step.