# Total fees between two dates

Hi Team,
We have the daily fees data as per date ranges. If we have two different dates (in and out) and how can we calculate total fees by referring the table in power query.
I have attached my manual calculation and expected result by referencing the fees table. thanks.
DifferentRatesBetweenTwoDates.xlsx (18.2 KB)

Regards,
Aye

So what I understood from your post here is that there is a reference table where you have fees mentioned based on a date range. And you wanted to dynamically calculate total fees for selected dated by referring to this table.

Other Experts here may have a simpler & a Better Solution. Here is my Solution.

• I created a Date Table [ for slicer] Extended Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum
• I created the Actual Dates Table based on the dates provided by you in the excel â€“ assuming that this is the entire range of dates you have to do fees calculation upon.
• I created the Fees Table â€“ Also as provided by you in the excel sheet. Here I made some additions.
• Created an Index Column
• Created a Group column and sorted it by index
• Now I went Back Again to Actual Dates Table and Created a Group Column in power Query using following m code: =
• Table.AddColumn(#â€śRemoved Other Columnsâ€ť, â€śCustomâ€ť, each if [Date] >= #date(2013, 3, 20) and [Date] <= #date(2013, 9, 19) then â€śGroup1â€ť else if [Date] >= #date(2013, 9, 20) and [Date] <= #date(2014, 3, 19) then â€śGroup2â€ť else if [Date] >= #date(2014, 3, 20) and [Date] <= #date(2014, 9, 19) then â€śGroup3â€ť else if [Date] >= #date(2014, 9, 20) and [Date] <= #date(2015, 3, 19) then â€śGroup4â€ť else if [Date] >= #date(2015, 3, 20) and [Date] <= #date(2015, 9, 19) then â€śGroup5â€ť else if [Date] >= #date(2015, 9, 20) and [Date] <= #date(2016, 3, 19) then â€śGroup6â€ť else if [Date] >= #date(2016, 3, 20) and [Date] <= #date(2016, 9, 19) then â€śGroup7â€ť else if [Date] >= #date(2016, 9, 20) and [Date] <= #date(2017, 3, 19) then â€śGroup8â€ť else if [Date] >= #date(2017, 3, 20) and [Date] <= #date(2017, 9, 19) then â€śGroup9â€ť else if [Date] >= #date(2017, 9, 20) and [Date] <= #date(2018, 3, 19) then â€śGroup10â€ť else if [Date] >= #date(2018, 3, 20) and [Date] <= #date(2018, 9, 19) then "Group11 "else null)
• Now is a to build relationships between the tables â€“ Please see the attached PBX file.
• Then the measures were simple calculations to get the result.
• Pbx file attached for your reference.

Aye.pbix (221.4 KB)

3 Likes

Welcome to the forum @Aye

Good to see that you are having progress with your work. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope youâ€™ll give your insights on how we can further improve the Support forum.

If not, how far did you get and what kind of help you need further?

By the way, love how you provided solution on this thread @jps. Appreciate your help on it.

1 Like

Many thanks, jps,