cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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:

 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...

1 ACCEPTED SOLUTION
Super User

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.

Super User

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors