locked
Budget with valid from and valid to dates RRS feed

  • Question

  • I am having a model with 4 tables in it:

    -Cost which shows projectnumber (called WBS), Costs in amount and the date for each record

    -Budget which shos the projectnumber, the total budget and when the total budget is valid from and valid to

    -Calendar table

    -Project dimension table

    I then need to allocate the budget to the projects, but my problem is allocating the budget without having a relation to the calendar table and also only calculating the budget when it is valid. I tried to make how the result should be:

     

    I tried to calculate whether the budget is valid or not by using the following formula:

    =Calculate(
    Sum(Budget[Budget]);
    Filter(Values(Budget[Valid from]);Budget[Valid from]<=Min(Calendar[Date]));
    Filter(Values(Budget[Valid to]);Budget[Valid to]>= Max(Calendar[Date]))
    )

    It returns the total budget in the periods where it is valid, but it doesn't help me much be calculating the daily budget. I also suspect that by doing the above I can't use the TOTALYTD formula afterwards as there is no relationship between the budget and calendar table?

    Any good ideas?

    Friday, August 18, 2017 12:42 PM

Answers

  • Hi Morten_DK,

    Thanks for your question.

    According to your description, you need to break budget table to each row of cost table.

    In this scenario, you can add a calculate column to Cost table called [valid From] as below:

    lookupvalue(Budget[Valid from],Budget[WBS],Cost[WBS])

    Adding a calculate column to Cost table called [valid To] as below:

    lookupvalue(Budget[Valid to],Budget[WBS],Cost[WBS])

    Adding a calculate column to Cost table called [Valid] as below:

    IF(Cost[Date] >= Cost[valid To] && Cost[Date] <= Cost[valid To],1,0)

    Adding a calculate column called Valid Days as below:

    calculate(sum(Cost[valid]),allexcept(Cost,Cost[WBS])

    Adding a calculate column to Cost table called [Daily Budget] as below:

    IF(Cost[Valid]=1,lookupvalue(Budge[Budget],Budget[WBS],Cost[WBS])/Cost[Valid Days],blank())

    Then you can do the other calculations as you want.


    Best Regards
    Willson Yuan
    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

    Monday, August 21, 2017 5:37 AM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With fixed budget date ranges for each project.
    http://www.mediafire.com/file/kcn4bu7trd95ku5/08_18_17b.xlsx
    http://www.mediafire.com/file/6ejtaj56c8louta/08_18_17b.pdf

    • Marked as answer by Morten_DK Tuesday, August 29, 2017 5:35 PM
    Saturday, August 19, 2017 4:31 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With fixed budget date ranges for each project.
    http://www.mediafire.com/file/kcn4bu7trd95ku5/08_18_17b.xlsx
    http://www.mediafire.com/file/6ejtaj56c8louta/08_18_17b.pdf

    • Marked as answer by Morten_DK Tuesday, August 29, 2017 5:35 PM
    Saturday, August 19, 2017 4:31 AM
  • Hi Morten_DK,

    Thanks for your question.

    According to your description, you need to break budget table to each row of cost table.

    In this scenario, you can add a calculate column to Cost table called [valid From] as below:

    lookupvalue(Budget[Valid from],Budget[WBS],Cost[WBS])

    Adding a calculate column to Cost table called [valid To] as below:

    lookupvalue(Budget[Valid to],Budget[WBS],Cost[WBS])

    Adding a calculate column to Cost table called [Valid] as below:

    IF(Cost[Date] >= Cost[valid To] && Cost[Date] <= Cost[valid To],1,0)

    Adding a calculate column called Valid Days as below:

    calculate(sum(Cost[valid]),allexcept(Cost,Cost[WBS])

    Adding a calculate column to Cost table called [Daily Budget] as below:

    IF(Cost[Valid]=1,lookupvalue(Budge[Budget],Budget[WBS],Cost[WBS])/Cost[Valid Days],blank())

    Then you can do the other calculations as you want.


    Best Regards
    Willson Yuan
    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

    Monday, August 21, 2017 5:37 AM
  • Thanks for both the suggestions, they work great. By doing it in Power Query I get the possibility to get a daily budget also on days where there are no cost. If I however get a new project I have to redo the query as it currently uses only references to the current projects when it unpivot/pivot them during the steps. Is it possible to get it to not use fixed references?

    I like the option of having it as DAX as it gives me more flexibility if we are to move the data model to Analysis Services, as I then don't rely on Power Query, but can I somehow make the calculation so days from the calendar table with valid from/to dates also gets a budget if the budget is valid?

     

    Tuesday, August 29, 2017 5:41 PM