How to calculate based on tariff bands

[PBChecking01]

Hi,

I have two tables:

1. Tariff Data 

2. Transactions Data

Tariff data is a report which consists of the below:

ID	Zone	Tariff Day	Start Time	End Time
A374581	A	Monday	08:30	19:30
A374582	A	Tuesday	08:30	19:30
A374583	A	Wednesday	08:30	19:30
A374584	A	Thursday	08:30	19:30
A374585	A	Friday	08:30	23:00
A374586	A	Saturday	08:30	23:00
A374587	A	Sunday	10:00	16:00
AA374598	AA	Monday	10:00	11:00
AA374599	AA	Tuesday	10:00	11:00
AA3745910	AA	Wednesday	10:00	11:00
AA3745911	AA	Thursday	10:00	11:00
AA3745912	AA	Friday	10:00	11:00
AA3745913	AA	Saturday
AA3745914	AA	Sunday
AA3745915	AA	Monday	15:00	16:00
AA3745916	AA	Tuesday	15:00	16:00
AA3745917	AA	Wednesday	15:00	16:00
AA3745918	AA	Thursday	15:00	16:00
AA3745919	AA	Friday	15:00	16:00
AA3745920	AA	Saturday
AA3745921	AA	Sunday
AS3760922	AS	Monday	09:00	11:00
AS3760923	AS	Tuesday	09:00	11:00
AS3760924	AS	Wednesday	09:00	11:00
AS3760925	AS	Thursday	09:00	11:00
AS3760926	AS	Friday	09:00	11:00
AS3760927	AS	Saturday
AS3760928	AS	Sunday
AS3760929	AS	Monday	14:00	16:00
AS3760930	AS	Tuesday	14:00	16:00
AS3760931	AS	Wednesday	14:00	16:00
AS3760932	AS	Thursday	14:00	16:00
AS3760933	AS	Friday	14:00	16:00
AS3760934	AS	Saturday
AS3760935	AS	Sunday
B3746036	B	Monday	09:00	18:00
B3746037	B	Tuesday	09:00	18:00
B3746038	B	Wednesday	09:00	18:00
B3746039	B	Thursday	09:00	18:00
B3746040	B	Friday	09:00	18:00
B3746041	B	Saturday
B3746042	B	Sunday
BB3746143	BB	Monday	10:00	11:00
BB3746144	BB	Tuesday	10:00	11:00
BB3746145	BB	Wednesday	10:00	11:00
BB3746146	BB	Thursday	10:00	11:00
BB3746147	BB	Friday	10:00	11:00
BB3746148	BB	Saturday
BB3746149	BB	Sunday
BB3746150	BB	Monday	15:00	16:00
BB3746151	BB	Tuesday	15:00	16:00
BB3746152	BB	Wednesday	15:00	16:00
BB3746153	BB	Thursday	15:00	16:00
BB3746154	BB	Friday	15:00	16:00
BB3746155	BB	Saturday
BB3746156	BB	Sunday
C3746257	C	Monday	10:00	19:30
C3746258	C	Tuesday	10:00	19:30
C3746259	C	Wednesday	10:00	19:30
C3746260	C	Thursday	10:00	19:30
C3746261	C	Friday	10:00	19:30
C3746262	C	Saturday	10:00	16:00
C3746263	C	Sunday
CC3746364	CC	Monday	10:00	11:00
CC3746365	CC	Tuesday	10:00	11:00
CC3746366	CC	Wednesday	10:00	11:00
CC3746367	CC	Thursday	10:00	11:00
CC3746368	CC	Friday	10:00	11:00
CC3746369	CC	Saturday
CC3746370	CC	Sunday
CC3746371	CC	Monday	15:00	16:00
CC3746372	CC	Tuesday	15:00	16:00
CC3746373	CC	Wednesday	15:00	16:00
CC3746374	CC	Thursday	15:00	16:00
CC3746375	CC	Friday	15:00	16:00
CC3746376	CC	Saturday
CC3746377	CC	Sunday

The transactions table consists of: (Each recored is an individual parking session and individual transaction)

Session Start Date	Session Start Time	Session Expiry Date	Session End Time	Zone
18/10/2022	11:12:01	18/10/2022	11:41:53	L
17/10/2022	09:08:47	17/10/2022	15:00:00	W
27/10/2022	15:13:36	27/10/2022	16:13:29	M
13/10/2022	08:52:25	13/10/2022	10:52:11	A
17/10/2022	12:27:17	17/10/2022	13:27:08	B
21/10/2022	09:22:15	21/10/2022	11:00:00	BB
04/10/2022	14:32:09	04/10/2022	16:00:00	CC
14/10/2022	09:56:47	14/10/2022	16:00:00	CC

I need to match each transaction record with a tariff. Example the transaction made on 04/10/2022 in zone CC should be matched with CC3746372 as the transaction was made on a tuesday between 14:32:09 to 16:00 it falls under the tariff CC3746372.

I have used the below dax to try calculate however some transactions are outside of the tariff start and end time example the session start time may be 14:00 and end time 16:00 the tariff start time may be 15:00 to 16:00, this transaction should mean they parked 1 hour within the tariff. As the other hour doesn't fall under the tariff band.

Tariff ID =

CALCULATE(VALUES(Tariff_Data[ID]),Transactions[TRANSACTION_ID],FILTER(Tariff_Data,Tariff_Data[Tariff Day]=Transactions[Day of Session Start]&&Tariff_Data[Zone]=Transactions[Zone]&&Tariff_Data[Loc for Staff Zone]=Transactions[LOT_ID] && Transactions[Session Start Time] >= Tariff_Data[Start Time] && Transactions[Session End Time] <= Tariff_Data[End Time]))

The above dax only gives results for transactions that exactly fall under a tariff start and end time.

Please can someone advise. Thank you

[andhiii079845]

I have used the below dax to try calculate however some transactions are outside of the tariff start and end time example the session start time may be 14:00 and end time 16:00 the tariff start time may be 15:00 to 16:00, this transaction should mean they parked 1 hour within the tariff. As the other hour doesn't fall under the tariff band.

-> But what is than the consquence, how should this been shown? Did the complete duration are related to tarif of the starting time? Which ID should be used? Can you make please a example for it.

If a transaction is complete in a time without a tarif like this, i would add here the time to make a calculation possible: 

CC3746369	CC	Saturday
CC3746370	CC	Sunday

like this: -> 

CC3746369	CC	Saturday	00:00	23:59
CC3746370	CC	Sunday	00:00	23:59

[PBChecking01]

Hi,

Thank you for your suggestion I have made this change.

I will give an example scenario:

The below are two different parking sessions made in zone CC:

04/10/2022	14:00:00	04/10/2022	16:00:00	37463	CC
14/10/2022	09:00:00	14/10/2022	16:00:00	37463	CC

Example 1:

The first transaction has started at 04/10/2022 at 14:32 to 04/10/2022 at 16:00 in zone CC on a Tuesday.

The below is the tariff data I have filtered out to Zone CC for Tuesday, which is when the sessions were made above.

ID	Zone	Tariff Day	Start Time	End Time	Cost of one hour staff parking	Cost of one Min
CC3746365	CC	Tuesday	10:00	11:00	1	0.017
CC3746372	CC	Tuesday	15:00	16:00	1	0.017

There for this calculation should work out that this user has parked during the 15:00 to 16:00 tariff (CC3746372) meaning it will cost £1 as the first hour is outside of the tariff (free time) and as they parked during 15:00 to 16:00 the cost of the parking session will be £1 meaning they should be allocated the CC3746372 session

Example 2

The second transaction has started on 04/10/2022 at 9:00 to 04/10/2022 at 16:00 in zone CC on a Tuesday.

Using the above tariff data this transaction has parked within two of the tariff bands 10:00 to 11:00 and 15:00 to 16:00 meaning the cost of this session will be £2.

I am trying to create a calculation which will help me build this calculation. Hopefully that makes sense 🙂