Get latest date and price in Month dimension


Can anyone guide me how to build and create line item formula to fulfil this requirement?
We have a flat data in excel, inside has date, product and price, the data can be entered and uploaded not in sequence, it means today i can upload for 6 Apr, tomorrow i can upload for 5 Apr.
I need a module then can shows the data by Month, indicate what is the latest date and price received for that product. Like the sample above, even though tomorrow i upload for 5 Apr, i still want to show 6 Apr because 6 Apr is the latest. Attached with a sample of input and expected output.




Best Answer

  • Amaya
    Answer ✓

    Hi @weilun.wong 


    This is similar Q&A 


    And in your case, since the output has Months as a dimension, the intermediate module must also have Months.

    To display the last day of the month, you would get the rank of the dates with the RANK function and then calculate the biggest RANK number of the month in the intermediate module for each product.



    You return the result to the flat file module then you know which data to aggregate as you have painted it yellow.


    The results can then be displayed by linking the output module with a MAX or SUM function.



    Hope this helps,



  • Hi @weilun.wong , 


    This one is a interesting one.

    • What is the amount of data you are loading ? 
    • Do you really require the daily data in Anaplan which is not the latest one?


    The approach used by me is a totally unconventional approach for this scenario. Please find below the approach 

    Step 1:

    Create a Data Module with 

    • Time Scale as Day; Product List as dimension
    • Line items : uploaded Date and Price (Set Summary of Uploaded Date as Last non blank and turn off summary for Price line item)
    • Load the Data in this module  (with 2 columns of date; 1 to map with Time scale other for line item)



    Step 2:

    Create another module with

    • Time Scale as Month; Product List as dimension
    • Line Items 
      • Updated Date : Refer the Last non blank line item in the previous module
      • Price : Refer the price with lookup of the updated date from current module



    This way you will get the expected results.


    There might be other workarounds. Looking forward to more inputs from others regarding the conventional approach aligning to the best practices on this thread.


    ~ Jitendra





  • Hi Jitendra,

    It is due to user want to see the historical price in system too, that's why need to keep all the data.
    Thanks for your solution.

  • Hi Taichi,

    Your solution looks good to me, it save the space of having a day dimension module to store the input, thanks for your help.