Forum Discussion

kukszi's avatar
kukszi
Helper 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 s...
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.

Jihwan_Kim's avatar
Jihwan_Kim
Super 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]
        )
)