Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NitinReja
Regular Visitor

Help with Data Modelling

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.

NitinReja_0-1661315376264.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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. 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.