Forum Discussion

jgalloway's avatar
jgalloway
Regular Visitor
9 years ago
Solved

Multiple LOOKUPVALUE dates

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....
Need Help
  • danrmcallister's avatar
    danrmcallister
    9 years ago

    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?

danrmcallister's avatar
danrmcallister
Resolver II
9 years ago

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's avatar
    jgalloway
    Regular Visitor
    9 years ago

    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