How to use FIRSTNONBLANK/LASTNONBLANK in a custom calendar


Hello everyone.


I have created a custom calendar.
I have mapped Week(List) and Year(List) corresponding to Time(Days).


I would like to create a module that sets the start and end dates to the Week(List) and Year(List).

I thought I could use the FIRSTNONBLANK and LASTNONBLANK function to get those dates, but it does not work.

Is there any good solution for this?





Best regards,


  • Misbah



    In your Calendar Master Module - Keep the Summary of Week (List) and Year (List) lines as FIRSTNONBLANK and see if you get the values get updated in the target module.



  • You will need to dimension your From and To by Time (this can be at Year level if you use the Summary mentioned by @Misbah )

  • I just tested and had to do the from date on a Day timescale, then needed a mapping to do a lookup from fake to real weeks.


    there must be a better way!

  • taku.s

    Hi, @MarkWarren 

    Thank you for your reply!


    What kind of module (or list) is this "year mapping"?


    best regards,

  • I created a module dimensioned by the Year list with a line item that is formatted to a year time period. In my test I just manually set but it wouldn't be too difficult to set up a formula there.
    This is to allow us to take the data we need out of the Time dimension...

  • taku.s

    Thank you for your reply.

    I will try it as a reference!


    best regards,