How to use FIRSTNONBLANK/LASTNONBLANK in a custom calendar

taku.s
taku.s
in Integrations and extensibility

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?

 

takushimozuru_1-1626264383148.pngtakushimozuru_2-1626264426801.png

 

 

Best regards,

Tagged:

Answers

  • Misbah
    Misbah

    @taku.s 

     

    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.

     

    Misbah

  • MarkWarren
    MarkWarren

    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 )

  • MarkWarren
    MarkWarren

    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.

    MarkWarren_0-1626271311533.png

    there must be a better way!

  • taku.s
    taku.s

    Hi, @MarkWarren 

    Thank you for your reply!

     

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

     

    best regards,

  • MarkWarren
    MarkWarren

    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
    taku.s

    Thank you for your reply.

    I will try it as a reference!

     

    best regards,

Categories