DAX - Graph budget changes over time

Question

I'm struggling to graph budget amount changes over time. &nbsp;The data that I have looks like:&nbsp;ProjectID &nbsp; | &nbsp; ActivityID &nbsp;| &nbsp; Budget &nbsp; | &nbsp; StartDate &nbsp; | &nbsp; LastUpdateDate &nbsp; | &nbsp;IsCurrent1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; | &nbsp;1,000 &nbsp; &nbsp; &nbsp; | &nbsp; 1/1/2017 &nbsp; &nbsp;| &nbsp; &nbsp;1/1/2017 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp;01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;| &nbsp;1,100 &nbsp; &nbsp; &nbsp; | &nbsp; 3/1/2017 &nbsp; &nbsp;| &nbsp; &nbsp;3/1/2017 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp;01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;| &nbsp;1,200 &nbsp; &nbsp; &nbsp; | &nbsp; 3/15/2017 &nbsp;| &nbsp; &nbsp;3/25/2017 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp;01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;| &nbsp;1,500&nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; 7/1/2017 &nbsp; &nbsp;| &nbsp; &nbsp;7/1/2017 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp;1&nbsp;&nbsp;My data model does have a date table that I can use for this. &nbsp;I've tried many different ways of doing this but none of them are returning the correct amount. &nbsp;Essentially my logic needs to be - Budget = Budget where StartDate &lt; Date &amp;&amp; (LastUpdateDate&gt;Date || IsCurrent = 1)&nbsp;Thanks in advance for any help you can provide.

neustupa2001 · Answer

I've gotten closer with&nbsp;testing = CALCULATE(&nbsp; SUM(FactBudgetHistory[BudgetCost]),&nbsp; &nbsp; LASTNONBLANK(view_DimDate[Date],&nbsp; &nbsp; &nbsp; CALCULATE(SUM(FactBudgetHistory[BudgetCost]))&nbsp; ))&nbsp;This is giving me the correct value if I'm just looking at one project and activity at a time but, it is not totaling up correctly.ProjectID | ActivityID | Budget | StartDate &nbsp; | LastUpdateDate | IsCurrent1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 1,000 &nbsp; &nbsp;| 1/1/2017 &nbsp; | 1/1/2017 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 800 &nbsp; &nbsp; &nbsp; | 1/12/2017 | 1/12/2017 &nbsp; &nbsp; &nbsp; &nbsp;| 1&nbsp;The above data example would give me a total of 800 for January but I need it to be 1,800 because these are different activities.

v-jiascu-msft · Answer

Hi&nbsp;Neustupa2001,&nbsp;Maybe you could try this formula:New =
CALCULATE (
    SUM ( FactBudgetHistory[Budgetcost] ),
    FILTER (
        ALLEXCEPT ( 'FactBudgetHistory', FactBudgetHistory[ProjectID] ),
        'FactBudgetHistory'[IsCurrent] = 1
    )
)Could you please tell me what the [date] is?Budget = Budget where StartDate &lt; Date &amp;&amp; (LastUpdateDate&gt;Date || IsCurrent = 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

v-jiascu-msft · Answer

Hi&nbsp;Neustupa2001,&nbsp;Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.Best Regards!Dale

neustupa2001 · Answer

I'm sorry for taking so long to get back to this. We have been trying to solve this with our data model by putting a daily value in there but I don't think that I going to be sustainable due to hundreds of thousands of records a day being inserted.

I believe I'm overcomplicating what I'm trying to accomplish here. The above is a dumbed down version of what I'm trying to do. Instead what I'm getting is

Forum Discussion

DAX - Graph budget changes over time

4 Replies

Related content

Working with Budgets

Budget, actual and variance matrix

Monthly sales budget data allocation to daily grain

Budget vs Actual

Creating Cumulative Graphs