Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

1 ACCEPTED SOLUTION
technolog
Super User
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. 

View solution in original post

1 REPLY 1
technolog
Super User
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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors