Use Filter Values to Proportion a Number

[pjmorris]

I have a list of budget values which are set for the year. I would like to be able to show the budget, but broken up to align with the calendar values on show - can anyone suggest either a measure that would do this or do I need to use PowerQuery to create daily values for each budget (lots) covering the period of interest.

For example, if I have a:

House Repairs at $1200 annually.

When I show actual summed on a monthly basis I would like to show House Repairs at $100 per month. Should I then choose to show the actual spend weekly the pivottable should show 7*1200/365.

Hope that makes sense

 

[ExcelGzh]

You are trying to get a pivot table to do something that it's not designed to do. Pivot tables deal with whole values that match other whole values (values assigned to particular dates analysed over a range of dates). They don't calculate proportional values for partial matches (values assigned to years analysed over fractions of a year).

 

[pjmorris]

Hi ExcelGzh, thanks for trying to help, however the key point of my query was that I'm using PowerQuery and PowerPivot. Powerpivot allows the creation of 'measures' that are much more flexible than the implicit calculations of an original Pivottable.

Since my original post I've arrived at the measure below:

=CALCULATE(
         sum(qryBudget[Annual])  *
          (1 + [end date]-Date(YEAR([early date]),1,1))  /
         (1 + Date(YEAR([end date]),12, 31)  -
                 Date(YEAR([early date]),1,1))
          )

Hope this makes sense.

Regards

 

[ExcelGzh]

I'm afraid I don't use PowerQuery or PowerPivot so I wouldn't know if that was a good solution or not.

Good luck with it.

 

[pjmorris]

PQ & PP are definitely worth learning, provides the ability to import a complete folder full of data (filtering for particular files if necessary), do consistent manipulation and then provide a really useful pivottable output. I've only just scratched the surface, but the effort to learn is definitely worth it.