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

[Victormar]

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 (),
VAR reading =

CALCULATETABLE (

VALUES ( READINGS[Reading_Id] ),
FILTER (

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

&& READINGS[Timestamp] > CONTRACT[START_DATE]

)

)
VAR energy =

CALCULATE (

SUM ( 'READINGS'[E1] )

- SUM ( 'READINGS'[E2] )
+ SUM ( 'READINGS'[E3] )
+ SUM ( 'READINGS'[E4] ),

READINGS[Reading_Id] IN reading
)

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.

Thanks in advance 🙂

[Victormar]

thanks to all!!! 🙂

[amitchandak]

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

[Victormar]

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 🙂

[v-yanjiang-msft · Accepted solution]

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' ),
        'Mileage Table'[READOUT_DATE]
            <= 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.