cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Reply
PBChecking01
Frequent Visitor

How to calculate based on tariff bands

‎03-20-2023 10:29 AM

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

Labels:
Message 1 of 3
202 Views
0
2 REPLIES 2
andhiii079845
Solution Sage
Solution Sage

‎03-20-2023 11:18 AM

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

 

Message 2 of 3
163 Views
0
PBChecking01
Frequent Visitor
In response to andhiii079845

‎03-21-2023 06:14 AM

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 🙂 

Message 3 of 3
139 Views
0

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

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

View All
Top Solution Authors
View All
Top Kudoed Authors
View All