locked
How to allocated budgeted costs over time RRS feed

  • Question

  • 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.
    Thursday, May 4, 2017 2:30 PM

Answers

  • Hi Nathanielpk,

    Do you mind share your sample table for further analysis?

    Thanks,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 11, 2017 9:33 AM

All replies

  • I see that people are looking at this, but nobody is answering. May I ask why? I am happy to provide additional info as needed. BTW, I could not figure out how to attach a file.
    Friday, May 5, 2017 1:15 AM
  • Hi Nathanielpk,

    dDates[day] is on day level? Please create a month column first. Then to get the first and last day on one month by creating 2 measures:

    FIRSTDATE = Calculate(MIN(dDates[day]),ALLEXCEPT(dDates,dDates[month]))
    LASTDATE = Calculate(MAX(dDates[day]),ALLEXCEPT(dDates,dDates[month]))

    Finally, please create your expected result.

    Cashflow2:=CALCULATE(SUM(devcosts[Monthly Cost]),FILTER(devcosts,devcosts[begin]<= [FirstDate])&&devcosts[Finish]>= [LastDate])))

    In addition, you can upload your example to OneDrive, and post the share link.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.





    Friday, May 5, 2017 2:07 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Mostly with M instead of DAX.
    http://www.mediafire.com/file/84slev8s3vm4khi/05_04_17b.xlsx
    http://www.mediafire.com/file/22ifug5xbhkled0/05_04_17b.pdf


    Friday, May 5, 2017 3:00 AM
  • Thanks for the response Angelia! I appreciate the guidance. Unfortunately, that did not work, but it likely has to do with my poor explanation. What's more I cannot include my One Drive link because my account is not verified. 

    I want to add up the daily cost per month,qtr, year for each task and during it's active time period. For example, if a task is open from 5/5/2017 to 6/30/2017 and it costs 100/day, the pivot table will show a cost of 2,600 in May and 3,000 in June. Thanks again! This has me very stumped!  

    Friday, May 5, 2017 8:25 AM
  • Hi Nathanielpk,

    Do you mind share your sample table for further analysis?

    Thanks,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 11, 2017 9:33 AM