Forum Discussion

kiranbrao's avatar
kiranbrao
New Member
9 years ago
Solved

Visualization for contract start/end dates and cost

I have several entries, each with the fields: organization name, contract start date, contract end date, and contract amount. I am trying to create a visual that will display organization contracts a...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi kiranbrao,

     

    You can create a calendar table:

     

    Calendar = CALENDAR(MIN('Table1'[Contract start date]),MAX('Table1'[Contract end date]))

     

    Then create a measure in fact table:

     

    Amount = CALCULATE(SUM('Table1'[Contract amount]),FILTER('Table1','Table1'[Contract start date]<=MAX('Calendar'[Date]) && 'Table1'[Contract end date]>=MAX('Calendar'[Date])))

     

     

     

    Please check attached .pbix file.

     

    Best Regards,
    Qiuyun Yu

     

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Share some data and show the expected result in a simple table format.

Shelley's avatar
Shelley
Post Prodigy
6 years ago

Ashish_MathurPlease note I manually created the cumulative usage in Excel above, but this is what I'd like Power BI to do and then draw in a graph, which each new contract starting at 0. See how I have the entitlement amount working correctly, with the help of this post, but now how do I get the actuals to accumulate towards the cap? Thanks for your help!