Dividing Two Running Total Columns in Power Pivot

[MCTampa]

I am trying to create a value called Average Weekly Rate (AWR) which is the calculated by dividing the running total of Revenue by the running total of Volume.

I have Power Pivot and actually did this successfully in August, but the file got corrupted and now I'm back to square one.
Here are the parameters I'm working with:

My data is in Access.
The data needs to go into a pivot table to be able to create booking curves based on various criteria.
All of my data is based on a field called Comp Date.

In a normal pivot table, I would have my Comp Date in my rows, and Revenue and Volume in my values with them being displayed as a running total in Comp Date.

My thought was that I could create a running total for Revenue and Volume in Power Pivot and just divide the two, but I cannot get the running total to work.

The formula I am using (for volume as an example) is:
Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(Output[Comp Date]),Output[Comp Date]<=max(Output[Comp Date])))

However this is just not working and I'm at a loss as to what is going on. When I bring volume into my pivot table, I am not getting a running total, just daily totals. I can only get the running total when I make it display as such and that does not allow me to accurately create my AWR.

Thanks for any assistance,
Mike

 

[MCTampa]

EDIT: Solved SOME of it ==>

When I changed
Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(Output[Comp Date]),Output[Comp Date]<=max(Output[Comp Date])))

Needed to be this:

Volume =calculate(sum(Output[SumOfRevenue Transaction Volume]),filter(all(Output),Output[Comp Date]<=max(Output[Comp Date])))

However my running totals for volume were collecting the data for ALL YEARS, not specific to their check in year.

Looking at a normal pivot table, this is what I want - just with the ability to divide the revenue by the volume.
I think the answer you guys will come up with is that this needs to be done in the data, but I can't do it that way because I can't really order my data in any sort of a sequence. I really need to be able to do this directly in the pivot table itself.

[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]2018
[/TD]
[TD][/TD]
[TD="align: right"]2017
[/TD]
[TD][/TD]
[TD="align: right"]2016
[/TD]
[TD][/TD]
[TD="align: right"]2015
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]Vol
[/TD]
[TD]Revenue
[/TD]
[TD]Vol
[/TD]
[TD]Revenue
[/TD]
[TD]Vol
[/TD]
[TD]Revenue
[/TD]
[TD]Vol
[/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]2/11/2016
[/TD]
[TD]2
[/TD]
[TD] 2,648
[/TD]
[TD][/TD]
[TD] -
[/TD]
[TD]1
[/TD]
[TD] 1,189
[/TD]
[TD]3
[/TD]
[TD] 5,303
[/TD]
[/TR]
[TR]
[TD]2/12/2016
[/TD]
[TD]5
[/TD]
[TD] 4,010
[/TD]
[TD]1
[/TD]
[TD] 1,294
[/TD]
[TD]2
[/TD]
[TD] 2,236
[/TD]
[TD]4
[/TD]
[TD] 6,377
[/TD]
[/TR]
[TR]
[TD]2/13/2016
[/TD]
[TD]6
[/TD]
[TD] 4,239
[/TD]
[TD]2
[/TD]
[TD] 2,270
[/TD]
[TD]3
[/TD]
[TD] 3,980
[/TD]
[TD]4
[/TD]
[TD] 6,377
[/TD]
[/TR]
[TR]
[TD]2/14/2016
[/TD]
[TD]14
[/TD]
[TD] 10,679
[/TD]
[TD]2
[/TD]
[TD] 2,270
[/TD]
[TD]4
[/TD]
[TD] 5,006
[/TD]
[TD]5
[/TD]
[TD] 7,651
[/TD]
[/TR]
[TR]
[TD]2/15/2016
[/TD]
[TD]15
[/TD]
[TD] 12,003
[/TD]
[TD]2
[/TD]
[TD] 2,270
[/TD]
[TD]9
[/TD]
[TD] 8,513
[/TD]
[TD]6
[/TD]
[TD] 8,608
[/TD]
[/TR]
[TR]
[TD]2/16/2016
[/TD]
[TD]17
[/TD]
[TD] 13,206
[/TD]
[TD]5
[/TD]
[TD] 4,679
[/TD]
[TD]10
[/TD]
[TD] 9,292
[/TD]
[TD]6
[/TD]
[TD] 8,608
[/TD]
[/TR]
[TR]
[TD]2/17/2016
[/TD]
[TD]28
[/TD]
[TD] 17,530
[/TD]
[TD]4
[/TD]
[TD] 4,138
[/TD]
[TD]12
[/TD]
[TD] 10,833
[/TD]
[TD]9
[/TD]
[TD] 11,782
[/TD]
[/TR]
[TR]
[TD]2/18/2016
[/TD]
[TD]28
[/TD]
[TD] 17,530
[/TD]
[TD]10
[/TD]
[TD] 8,254
[/TD]
[TD]12
[/TD]
[TD] 10,833
[/TD]
[TD]9
[/TD]
[TD] 11,782
[/TD]
[/TR]
[TR]
[TD]2/19/2016
[/TD]
[TD]30
[/TD]
[TD] 19,158
[/TD]
[TD]10
[/TD]
[TD] 9,764
[/TD]
[TD]15
[/TD]
[TD] 12,957
[/TD]
[TD]13
[/TD]
[TD] 18,951
[/TD]
[/TR]
[TR]
[TD]2/21/2016
[/TD]
[TD]32
[/TD]
[TD] 20,436
[/TD]
[TD]11
[/TD]
[TD] 10,713
[/TD]
[TD]16
[/TD]
[TD] 13,256
[/TD]
[TD]15
[/TD]
[TD] 22,307
[/TD]
[/TR]
[TR]
[TD]2/22/2016
[/TD]
[TD]36
[/TD]
[TD] 27,012
[/TD]
[TD]16
[/TD]
[TD] 16,715
[/TD]
[TD]17
[/TD]
[TD] 14,282
[/TD]
[TD]17
[/TD]
[TD] 24,042
[/TD]
[/TR]
[TR]
[TD]2/23/2016
[/TD]
[TD]38
[/TD]
[TD] 28,010
[/TD]
[TD]16
[/TD]
[TD] 16,715
[/TD]
[TD]21
[/TD]
[TD] 17,042
[/TD]
[TD]18
[/TD]
[TD] 24,549
[/TD]
[/TR]
[TR]
[TD]2/24/2016
[/TD]
[TD]40
[/TD]
[TD] 29,802
[/TD]
[TD]17
[/TD]
[TD] 18,679
[/TD]
[TD]28
[/TD]
[TD] 24,560
[/TD]
[TD]18
[/TD]
[TD] 24,549
[/TD]
[/TR]
[TR]
[TD]2/25/2016
[/TD]
[TD]46
[/TD]
[TD] 37,216
[/TD]
[TD]18
[/TD]
[TD] 19,926
[/TD]
[TD]29
[/TD]
[TD] 25,699
[/TD]
[TD]18
[/TD]
[TD] 24,549
[/TD]
[/TR]
[TR]
[TD]2/26/2016
[/TD]
[TD]47
[/TD]
[TD] 38,065
[/TD]
[TD]20
[/TD]
[TD] 21,144
[/TD]
[TD]29
[/TD]
[TD] 25,699
[/TD]
[TD]19
[/TD]
[TD] 25,030
[/TD]
[/TR]
[TR]
[TD]2/27/2016
[/TD]
[TD]51
[/TD]
[TD] 39,251
[/TD]
[TD]23
[/TD]
[TD] 25,306
[/TD]
[TD]30
[/TD]
[TD] 26,700
[/TD]
[TD]21
[/TD]
[TD] 27,128
[/TD]
[/TR]
[TR]
[TD]2/28/2016
[/TD]
[TD]57
[/TD]
[TD] 43,283
[/TD]
[TD]28
[/TD]
[TD] 28,642
[/TD]
[TD]30
[/TD]
[TD] 26,700
[/TD]
[TD]21
[/TD]
[TD] 27,128
[/TD]
[/TR]
[TR]
[TD]2/29/2016
[/TD]
[TD]61
[/TD]
[TD] 45,814
[/TD]
[TD]28
[/TD]
[TD] 28,642
[/TD]
[TD]33
[/TD]
[TD] 29,006
[/TD]
[TD]22
[/TD]
[TD] 27,614
[/TD]
[/TR]
[TR]
[TD]3/1/2016
[/TD]
[TD]66
[/TD]
[TD] 48,299
[/TD]
[TD]31
[/TD]
[TD] 31,421
[/TD]
[TD]37
[/TD]
[TD] 31,490
[/TD]
[TD]24
[/TD]
[TD] 29,249
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gazpage]

You may save yourself some stress if you just look up the TOTALYTD function. Create two measures using that, one for sales and one for volume, and then a third which is one divided by the other.

 

[gazpage]

https://www.daxpatterns.com/time-patterns/

This shows the first principles way of doing YTD. Still create two measures and divide them though.

 

[MCTampa]

I'm not sure this is how I want to go about it.
I've already created my data in Access.
Because I'm building a booking curve what I want to do is have everything laid out in a date-equivalent manner.
For example: because we are in the year 2017 (for a few more days) I want the x-axis of my chart to reference 2017 dates. For bookings made in 2016, I adjust the booking date to a "comp" date which allows me to equate it to a day in 2017.

In the table above, I scrolled through my data to capture a section which had sufficient information for display purposes.
In the above table I am showing the following:

Bookings made on a specific day as noted on the side, for occupancy in a specific year, as noted across the top.
Bookings made in 2016 for 2017 occupancy showing their actual 2016 booking date. Since 2017 is the current year, this serves as the basis for my model. (I use a Year, Year-1, Year-2 and Year+1 approach, so it's not based on the actual numerical year).
I also show bookings made in 2015 for 2016 occupancy based on a 2016 comp date as well as bookings made in 2014 for 2015 occupancy based on the same 2016 equivalent date. Lastly I also have bookings made in 2017 for 2018 occupancy, again adjusted to a 2016 date so everything lines up.

What governs my data is the current year (2017) and all activity into that year. Activity into prior or future years is always 'adjusted' or 'comped' to a date which allows everything to be charted on one graph.

Since I have already built this into my data in Access, I do not think the approach you are showing me will work.
Can I send you my data so you might better understand what I'm working with?

 

[gazpage]

Happy to look at it. I think you need to explain mechanically what you need. Ie “a table with the months down the side and then a column showing the YTD sum of sales divided by YTD volumes”.