How to allocated budgeted costs over time

Anything related to PowerPivot and DAX Formuale
nathanielpk
Posts: 1
Joined: Thu May 04, 2017 3:06 pm

How to allocated budgeted costs over time

Postby nathanielpk » Thu May 04, 2017 3:21 pm

Hello,

I am stuck and hoping I can find some help here.

I have a budget built off unit costs and quantities. Additionally, each line item occurs in conjunction with a task which occurs over a defined time period. I would like to build a dax formula to allocated a monthly cost for each line if it is active in that month. I have attached my file.

Here is the formula I am using right now:

Cashflow2:=CALCULATE(SUM(devcosts[Monthly Cost]),FILTER(devcosts,devcosts[begin]>MIN(dDates[day])&&devcosts[Finish]<=MAX(dDates[day])))

In my pivot table this only returns the single amount in the Monthly Costs column rather than adding an entry for each month where the task is "Active"

Appreciate all advice. Thanks.

MattAllington
Posts: 836
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to allocated budgeted costs over time

Postby MattAllington » Sat May 06, 2017 4:27 pm

There is great coverage here http://www.daxpatterns.com/budget-patterns/
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest