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)
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 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.
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.
Did the solution above help your inquiry? If it does, kindly mark as solution the answer that solved your query.
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.
