Help with Data Modelling

[NitinReja]

Hello Experts,

Need your help. I am trying to create a P&L view for my company through several excel based data sources. One such source relates to conveyance expense. I have a file that lists monthly expense in a datatable. Problem is that the month here is 16th-15th for eg. Expense dated 20th May is actually 16th April - 15th May and is paid on 20th May. Since I want to be able to present data in quarterly/monthly splits, I am trying to capture half of the expense in April and other half in May (staying with above example). Unfortunately, I am unable to think of the solution to do this.

My table looks like below. To be clear on my need- If you look at second row, the fuel amount is 4245.5 with start date 15th April and end date 15th May. I want to find a way through which I can capture 2122.75 (4245.5/2) in April as well as May each.

[tamerj1 · Accepted solution]

Hi @NitinReja 

using power query you can for example duplicate the query. For one query delete the end date and rename the start date as date. For the other query delete the start date and rename the end date as date. Append both queries then add two new columns calculating half of km and half of fuel amount and delete the old two columns.