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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBChecking01
Frequent Visitor

How to calculate based on tariff bands

Hi,

 

I have two tables:

1. Tariff Data 

2. Transactions Data

 

Tariff data is a report which consists of the below:

IDZoneTariff DayStart TimeEnd Time
A374581AMonday08:3019:30
A374582ATuesday08:3019:30
A374583AWednesday08:3019:30
A374584AThursday08:3019:30
A374585AFriday08:3023:00
A374586ASaturday08:3023:00
A374587ASunday10:0016:00
AA374598AAMonday10:0011:00
AA374599AATuesday10:0011:00
AA3745910AAWednesday10:0011:00
AA3745911AAThursday10:0011:00
AA3745912AAFriday10:0011:00
AA3745913AASaturday  
AA3745914AASunday  
AA3745915AAMonday15:0016:00
AA3745916AATuesday15:0016:00
AA3745917AAWednesday15:0016:00
AA3745918AAThursday15:0016:00
AA3745919AAFriday15:0016:00
AA3745920AASaturday  
AA3745921AASunday  
AS3760922ASMonday09:0011:00
AS3760923ASTuesday09:0011:00
AS3760924ASWednesday09:0011:00
AS3760925ASThursday09:0011:00
AS3760926ASFriday09:0011:00
AS3760927ASSaturday  
AS3760928ASSunday  
AS3760929ASMonday14:0016:00
AS3760930ASTuesday14:0016:00
AS3760931ASWednesday14:0016:00
AS3760932ASThursday14:0016:00
AS3760933ASFriday14:0016:00
AS3760934ASSaturday  
AS3760935ASSunday  
B3746036BMonday09:0018:00
B3746037BTuesday09:0018:00
B3746038BWednesday09:0018:00
B3746039BThursday09:0018:00
B3746040BFriday09:0018:00
B3746041BSaturday  
B3746042BSunday  
BB3746143BBMonday10:0011:00
BB3746144BBTuesday10:0011:00
BB3746145BBWednesday10:0011:00
BB3746146BBThursday10:0011:00
BB3746147BBFriday10:0011:00
BB3746148BBSaturday  
BB3746149BBSunday  
BB3746150BBMonday15:0016:00
BB3746151BBTuesday15:0016:00
BB3746152BBWednesday15:0016:00
BB3746153BBThursday15:0016:00
BB3746154BBFriday15:0016:00
BB3746155BBSaturday  
BB3746156BBSunday  
C3746257CMonday10:0019:30
C3746258CTuesday10:0019:30
C3746259CWednesday10:0019:30
C3746260CThursday10:0019:30
C3746261CFriday10:0019:30
C3746262CSaturday10:0016:00
C3746263CSunday  
CC3746364CCMonday10:0011:00
CC3746365CCTuesday10:0011:00
CC3746366CCWednesday10:0011:00
CC3746367CCThursday10:0011:00
CC3746368CCFriday10:0011:00
CC3746369CCSaturday  
CC3746370CCSunday  
CC3746371CCMonday15:0016:00
CC3746372CCTuesday15:0016:00
CC3746373CCWednesday15:0016:00
CC3746374CCThursday15:0016:00
CC3746375CCFriday15:0016:00
CC3746376CCSaturday  
CC3746377CCSunday  

 

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

Session Start DateSession Start TimeSession Expiry DateSession End TimeZone
18/10/202211:12:0118/10/202211:41:53L
17/10/202209:08:4717/10/202215:00:00W
27/10/202215:13:3627/10/202216:13:29M
13/10/202208:52:2513/10/202210:52:11A
17/10/202212:27:1717/10/202213:27:08B
21/10/202209:22:1521/10/202211:00:00BB
04/10/202214:32:0904/10/202216:00:00CC
14/10/202209:56:4714/10/202216:00:00CC

 

 

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

2 REPLIES 2
andhiii079845
Super User
Super User

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: 

CC3746369CCSaturday  
CC3746370CCSunday  

 

like this: -> 

CC3746369CCSaturday00:0023:59
CC3746370CCSunday00:0023:59

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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/202214:00:0004/10/202216:00:0037463CC
14/10/202209:00:0014/10/202216:00:0037463CC

 

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.

IDZoneTariff DayStart TimeEnd TimeCost of one hour staff parkingCost of one Min
CC3746365CCTuesday10:0011:0010.017
CC3746372CCTuesday15:0016:0010.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 🙂 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.