cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure to return value for same location and between start and end date

Hi there, 

 

Appreciate any help I can get with the problem I am trying to solve below. 

 

I am building a Power BI model that estimates the demand for water for a number of different sources. Each 'source' of demand for water has it's own excel sheet containing 'factors' relevant to that source of demand. The 'factors' are pulled into the model and generally multiplied against a baseline data set.  

 

One of the demand sources I am struggling with is for the closure of the plant. The 'factors' that I am given to work with for this 'source' of demand is in the format below:

LocationStart YearNo. of YearsDemand per year
A2020102
B20502010
C203022
Dnull00
Enull00

 

The baseline data I am working with is in the following format:

LocationYearCateogory 1
A2020A
A2021A
A2022A
A...A
A2020B
A2021B
A2022B
A...B
B2020A
B2021A
B...A
B2020B
B2021B
B...B
.........

 

I am trying to write a measure so that in the dashboard, whenever I select a particular location (A,B,...) and year (2020,2021,...) the measure will:

  1. Look up the 'location' in the lookup table,
  2. Work out if the current year (in the baseline data) is between the 'start year' and the 'start year' + 'no. of years' in the lookup table
  3. Return the number in the 'demand per year' column in the lookup table

 

Let me know if you have any thoughts! I can't seem to find anything relevant online and not sure how to start...

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors