cancel
Showing results for
Did you mean:
Frequent Visitor

Dealing with daily reports, calculate prorated values in the report when spanning over month end.

Hi,
I've been struggling with this the whole day and would like some help.

I have table with daily reports with a start_date_utc and finish_date_utc. The report includes values for distance travelled in each report. The report includes all vessels identified by the their imo numbers. My end report will be sliced by the vessel name or imo number.
I'd like to calculate the total distance travelled for each month. I've created a date table, and calculated column in the main table to get just the date component from the datetime value of finish_date_utc.

the problem is that at the month end,  (and month beginning) the report spans over the month.
For the month end report, I'd like to get the value of distance travelled from the next report, which falls in the next month. The distance is to calculated for the report period, and added to the month end reported distance in a prorated value.
For the beginning of the month, thus i'd like to calculate just the proportionate distance that is included in the current month.

for eg, if the report at the month end ended at 1600 hours, the distance travelled for the next 8 hours is to be calculated by first finding the next reported distance, say 200 miles in 15 hours, so the distance to be included in the month is 8*200/15.

2 REPLIES 2
Super User

@onqufist , refer if this can help

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Frequent Visitor

Thanks for the tip. Though I did come across your article before, haven't wrapped my head around how to effectively use it.

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors