Multiple LOOKUPVALUE dates

[jgalloway]

Hi all,

I'm an avid user of Excel for prototyping before moving onto Power BI or custom build dashboards, but seem to be getting stuck at something pretty simple here when moving this project on...

Two tables: Periods, Dates. Period has the below data...

Dates has this data:

The dates table is the centre of relatonships between all the data sets in this project therefore it's important that it connects with the periods table and tells me which period year and which period num it's in. Normally this would be along the lines of:

=INDEX ( PeriodsTable[PeriodNum], MATCH(

         LARGE( IF( PeriodTable[StartDate]>=DatesTable[Date], IF( PeriodTable[EndDate]<=DatesTable[Date], PeriodTable[Index])),

                   PeriodsTable[Index],0)

              )

Along those lines would have found me the index number and provided the Period Num based on the date column being bigger or equal to PeriodTable[StartDate] and smaller or equal to PeriodTable[EndDate].

Initially I thought I'd work this in Power BI Desktop as:

=LOOKUPVALUE( PeriodTable[PeriodNum], DatesTable[Date], >= PeriodTable[StartDate], DatesTable[Date], <= PeriodTable[EndDate] )

...but of course that's just MATCH MATCH exact if you take out ">=" (as you can't use it in that formula)...whereas I need to check "is this date between the result in column X and column Y".

Any advice on the best solution?

Note: its purely a reference and there's nothing which is usable as a relationship.

[danrmcallister · Accepted solution]

Try something like this:

Period Number = Calculate(sum(Periods[PeriodNum]),
	Filter(Periods,Periods[Start] <= 
		LASTDATE('Date'[Date].[Date])
		&&
		Periods[End] >= 
		FIRSTDATE('Date'[Date].[Date])
		)
)

This works so long as you're looking for a number - AFAIK this wouldn't work with text, maybe there's a different function that would work with LASTDATE/FIRSTDATE as filters though.

Is that what you're looking for?

[jgalloway]

danrmcallister,

That worked perfect thanks. I just have to overlap with a LOOKUPVALUE, but in reality it's best to let the table relationships handle that.

Thanks again