Forum Discussion

Neustupa2001's avatar
Neustupa2001
Frequent Visitor
9 years ago

DAX - Graph budget changes over time

I'm struggling to graph budget amount changes over time.  The data that I have looks like:   ProjectID   |   ActivityID  |   Budget   |   StartDate   |   LastUpdateDate   |  IsCurrent 1           ...
Neustupa2001's avatar
Neustupa2001
Frequent Visitor
9 years ago

I've gotten closer with

 

testing = CALCULATE(
  SUM(FactBudgetHistory[BudgetCost]),
    LASTNONBLANK(view_DimDate[Date],
      CALCULATE(SUM(FactBudgetHistory[BudgetCost]))

  )
)

 

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   | LastUpdateDate | IsCurrent
1              | 1              | 1,000    | 1/1/2017   | 1/1/2017           | 1
1              | 2              | 800       | 1/12/2017 | 1/12/2017        | 1

 

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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Neustupa2001,

     

    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 < Date && (LastUpdateDate>Date || IsCurrent = 1)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale