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

[ElHeatho]

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:

Location	Start Year	No. of Years	Demand per year
A	2020	10	2
B	2050	20	10
C	2030	2	2
D	null	0	0
E	null	0	0

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

Location	Year	Cateogory 1
A	2020	A
A	2021	A
A	2022	A
A	...	A
A	2020	B
A	2021	B
A	2022	B
A	...	B
B	2020	A
B	2021	A
B	...	A
B	2020	B
B	2021	B
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...

[technolog · Accepted solution]

First, you'll want to create a relationship between your baseline data and your lookup table based on the 'Location' column. This will allow you to reference the lookup table's columns in your DAX measure.

Next, for the measure, you'll want to use the RELATED function to pull the related values from the lookup table based on the selected location in the baseline data.

The logic you want to implement is to check if the current year in the baseline data is within the range of the 'Start Year' and 'Start Year' + 'No. of Years'. If it is, then you'll return the 'Demand per year' value, otherwise, you'll return 0.

Here's a DAX measure that should do the trick:

Water Demand Measure =
VAR CurrentYear = SELECTEDVALUE('Baseline Data'[Year])
VAR StartYear = RELATED('Lookup Table'[Start Year])
VAR Duration = RELATED('Lookup Table'[No. of Years])
VAR Demand = RELATED('Lookup Table'[Demand per year])

RETURN
IF(
AND(CurrentYear >= StartYear, CurrentYear <= StartYear + Duration),
Demand,
0
)
In this measure, we're first defining some variables to make the formula easier to read. We're grabbing the current year from the baseline data, and then using the RELATED function to get the corresponding 'Start Year', 'No. of Years', and 'Demand per year' values from the lookup table.

The IF and AND functions are then used to check if the current year is within the range defined by the 'Start Year' and 'Start Year' + 'No. of Years'. If it is, we return the 'Demand per year' value, otherwise, we return 0.

Once you've created this measure, you can use it in your dashboard to display the water demand for any selected location and year.