Total fees between two dates

Power Query
2

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