Latest Value Per Month
I have a cube by Employee, Cost Center and Day. I would like to populate a cube which is by Employee, Cost Center and Month with the latest value (as close to the last day of the month as possible) for each Employee, Cost Center combination as shown by screenshot. Doing an extract reload unfortunately does not work as there is too many rows when all employees are selected (I have tried to narrow the selection before the extract reload too). How would I achieve this using dataflows? I have played around quite a bit with "last value" time function but could not get it to work.
Answers

Hi Yas,
Thanks for posting the question. There would probably be a few different ways to do this. One way I can think of would be the following: 1) Apply your main time range selection (the months you need to run this process for). 2) Have an integer temp cube structured by day and populate it with a dataflow a=1. Another int temp cube is structured by month and set it to a=b (where b is the day cube populated before). This will flow the number of days in each month into the second temp cube. 3) Have a data flow step with a layout where you drag your month integer temp cubes from above (block "a"), and the day entity (block "b"). In the expression, put the following algorithm to populate your temp 1 cube you had created in step 1: IF(DAY(b)=a, 1, 0). This will basically flag the last day of each month in a temp cube by day for you. Then you can use this temp cube in a Select Entity Based on Cube step to select only those flagged days before your main dataflow, so you only flow the data from the days you care about into your month cube.
Kind regards,
Hamza
1 
Hi Yas,
There are two last value functions, one is analytics and the other is the time function. The time function should perform the purpose you're trying to achieve, if you're not concerned about seeing 0s as a last value. Board doesn't recognise zero as a value at the lowest level so Hamza's solution may work for this case.
Thanks,
Scott
2
Categories
 All Categories
 2K Forums
 1.8K Platform
 173 Academy
 332 Resources
 1 Board Knowledge Base
 50 Best Practices
 49 HowTo Guides
 19 Board Advocacy Program
 197 Blog
 4 Groups Hub
 4 About Groups
 New Community Members
 Board Community Captains
 DACH
 Japan
 5 Community Captains
 1 About Community Captains
 3 Meet the Community Captains
 1 Topics & Thought Starters
 Learn from the Board Captains
 Release Notes
 Academy
 2 Board Academy
 8 ILT/VILT Course Catalogue
 13 eLearning Course Catalogue
 4 Academy Forum
 1.3K Idea Exchange
 341 Partner Hub
 94 Support
 14 FAQ's
 Customer Support Portal
 54 Support Articles
 BEAP