cancel
Showing results for
Did you mean:
Helper II

## 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
Helper II

thanks to all!!! 🙂

Super User

@Victormar ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But I think the approach in these two blogs should help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Helper II

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

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!