Forum Discussion

kukszi's avatar
kukszi
Icon for Helper I rankHelper I
3 years ago
Solved

Lookup from a range

Hi All,

It might be a basic question, but I'm not sure how to solve the below problem therefore I'm asking for the help of the Community. I have two calendar tables: one that includes all the days starting from April 1, 2021, and the second one includes date ranges (it is a custom financial calendar with 13 periods where Period 1 starts each years on April 1st, and Period 13 ends each years on March 31st).

 

Calendar_Table:
Day
Apr 1, 2021
Apr 2, 2021
...
Apr 25, 2021
Apr 26, 2021
...


Fiscal_Year_Table:
Per Start Day | Per End Day | Period
Apr 1, 2021 | Apr 25, 2021 | P01 F22
Apr 26, 2021 | May 23, 2021 | P02 F22
...


I'd like to combine the two tables, and would like to get the following results:

Day | Period
Apr 1, 2021 | P01 F22
Apr 2, 2021 | P01 F22
...
Apr 25, 2021 | P01 F22
Apr 26, 2021 | P02 F22
...


If I'm doing with LOOKUPVALUE formula, then it populates only the start day and all other values will be empty:

Day | Period
Apr 1, 2021 | P01 F22
Apr 2, 2021 | (blank)
...
Apr 25, 2021 | (blank)
Apr 26, 2021 | P02 F22
...

What is the best way to populate the financial periods for all dates?

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Write this calculated column formula in Calendar_Table

    =calculate(max(Fiscal_Year_Table[Period]),filter(Fiscal_Year_Table,Fiscal_Year_Table[Per start day]<=earlier(calendar_table[day])&&Fiscal_Year_Table[Per end day]>=earlier(calendar_table[day])))

    Hope this helps.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a new table.

    I tried to create a sample pbix file like below.

     

     

     

    New Calendar Table =
ADDCOLUMNS (
    DISTINCT ( 'Calendar'[Date] ),
    "Period",
        MAXX (
            FILTER (
                Fiscal_Year,
                Fiscal_Year[Per start day] <= 'Calendar'[Date]
                    && Fiscal_Year[Per end day] >= 'Calendar'[Date]
            ),
            Fiscal_Year[Period]
        )
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Write this calculated column formula in Calendar_Table

    =calculate(max(Fiscal_Year_Table[Period]),filter(Fiscal_Year_Table,Fiscal_Year_Table[Per start day]<=earlier(calendar_table[day])&&Fiscal_Year_Table[Per end day]>=earlier(calendar_table[day])))

    Hope this helps.

  • kukszi's avatar
    kukszi
    Icon for Helper I rankHelper I
    3 years ago

    Thank you both! Both solutions are working correctly; however, Ashish_Mathur's version works better in my case, as I wanted to adjust an existing table instead of creating a new one.