Help with Calendar - that looks at minutes to prorate

[kathycmiller]

I am trying to prorate data between 2 dates.  I have a calendar table - cross joined to my data table filterning the two dates Start and End.  My probelm is that I need to calculate revenue based on partial days for each line item.  For example

Row1
Start: 2/26/2020 5:18  End: 4/27/2020 3:24.  Total revenue: 7000.  Gross Days =60.92  

Row 2

Start: 4/27/2020 3:24  End: 6/22/2020 4:32.  Total Revenue: 4500.  Gross Days=56.63  

My problem- Sums total fine for full days, but days that are partial (example 4/27/2020) to prorate at the hourly/minute level.  

Is there a better way to do this?  Perhaps a cross joined table is not the answer, but I was unable to attain what I needed by just linking my calendar table directly to my data.  Any advice would be apprecited.

[Greg_Deckler]

@kathycmiller - It's difficult say what is going wrong based on the current information. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[kathycmiller]

Start	Stop	Gross Day	Revenue	Rev Per Day
1/1/2020 18:00	2/26/2020 13:30	56.08333	3454	61.58692
2/26/2020 13:30	4/27/2020 3:24	60.92083	2346	38.50899

Hopefully this helps a bit.  I am trying to calc revenue based on start/stop time for the month/quarter/year.

I would like to see:

Jan 1:  revenue = 15.39673 (1/4 of the day)
Jan 2-Feb 25: revenue = 61.586
Feb 26: Partial revenue from row 1 & row 2
Feb 27-Apr 26: revenue = 38.508 per day...

Hopefully this helps.  (Note that Gross days is not Stop -Start)

[Greg_Deckler]

@kathycmiller - @Anonymous may be correct about power query versus DAX but I am not sure how to approach it in Power Query. However, I believe I have done something in DAX that might fit the bill: Hours Breakdown: https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306

Breaks a Start date/time and End date/time down to the minutes within each hour. Should be adaptable to this situation I believe. Check it out and see what you think.

@ImkeF  and @edhans might have some suggestions around how to accomplish this in Power Query. They tend to deal in solutions versus admonishments.

[ImkeF]

Hi @kathycmiller ,

please check attached solution.

The allocated amounts don't add up to the original amounts, because (as you've stated) the GrossDays are not Stop-Start.

But if you consider Jan 1st Revenue as 1/4  of the day (which accords to the actual Stop-Start-duration) - the resulting shares won't add up to the GrossDays that determined the daily amount.

[Anonymous]

Please calculate this in Power Query. In DAX, even though possible, it'll be painful and the measure will be slow.

Best
D