cancel
Showing results for
Did you mean:
Super User

## How to create a measure with a reference column as a date filter

Hi all,

I would like to create different measures with an If clause referred to a date.

I have a list of vehicles with a contract start date, and I am calculating measures like average speed, energy consumption, etc, for every year of the contract.

My idea would be to calculate the measures for every year only if a full year contract has been achieved, something like:

Net Energy Consumption (KWh) 1st year =

IF (

CONTRACT[START_DATE] + ( 365.25 * 1 )
< TODAY (),

CALCULATETABLE (

FILTER (

READINGS[Timestamp] < CONTRACT[START_DATE] + ( 365.25 * 1 )

)

)
VAR energy =

CALCULATE (

)

RETURN

energy,

BLANK ()

)

I want the different reading I have for the different values to be in the years of the contract, not our natural year (jan-dec), and the date being after the START_DATE. Also I want to apply this calculation for the coming years, this is why I add a "1", to reuse the formula (although I know it's not so elegant).

My main problem is that I cannot reference the column START_DATE in a measure.

1 ACCEPTED SOLUTION
Community Support

Hi @Victormar ,

If you want to get the max mileage of each vehicle_ID within the contract_start_date after adding one year, here's my solution.

1.Create relationship between the two tables with the vehicle_ID column.

2.Create a measure.

``````Max =
MAXX (
FILTER (
ALL ( 'Mileage Table' ),
<= DATE ( YEAR ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ) + 1, MONTH ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ), DAY ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ) )
&& 'Mileage Table'[VEHICLE_ID] = MAX ( 'Mileage Table'[VEHICLE_ID] )
),
'Mileage Table'[MILEAGE]
)
``````

Put the vehicle_ID from the contract table and the measure in a visual, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Super User

thanks to all!!! 🙂

Super User
Super User

I have been thinking about it, and it might serve my purpose to be able to get the maximum value of a column (mileage) for each vehicle within the range of the first perior of their contract, which is in another table (contracts table has the starting date of the contract, and I would add 1 year to it):

 CONTRACT TABLE MILEAGE TABLE VEHICLE_ID CONTRACT_START_DATE VEHICLE_ID READOUT_DATE MILEAGE 1 01/01/2021 1 01/09/2021 30000 2 02/02/2021 1 05/01/2021 20000 3 04/01/2021 1 02/02/2022 70000 4 03/05/2019 1 01/04/2021 25000 5 04/01/2018 2 03/04/2022 40000 2 05/02/2021 1000 2 06/06/2021 10000 2 07/11/2021 25000 3 07/01/2021 1000 3 06/04/2021 10000 3 06/03/2022 35000

Exemple attached of what I want

Thanks again 🙂

Community Support

Hi @Victormar ,

If you want to get the max mileage of each vehicle_ID within the contract_start_date after adding one year, here's my solution.

1.Create relationship between the two tables with the vehicle_ID column.

2.Create a measure.

``````Max =
MAXX (
FILTER (
ALL ( 'Mileage Table' ),
<= DATE ( YEAR ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ) + 1, MONTH ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ), DAY ( MAX ( 'Contract Table'[CONTRACT_START_DATE] ) ) )
&& 'Mileage Table'[VEHICLE_ID] = MAX ( 'Mileage Table'[VEHICLE_ID] )
),
'Mileage Table'[MILEAGE]
)
``````

Put the vehicle_ID from the contract table and the measure in a visual, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors