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_MathurHere's an example of the usage data: 

Order Submit DateList_PriceCumulative UsageNotes
2/21/2018$2,592$2,592Usage begins at 0 on 2/1/18
8/23/2018$1,542$4,134 
10/12/2018$1,709$5,843 
10/17/2018$3,893$9,736 
12/4/2018$513$10,249 
2/25/2019$2,940$2,940Usage begins at 0 again on 2/1/19
5/14/2019$1,186$4,126 
7/18/2019$0$4,126 
8/23/2019$323$4,449 
9/2/2019$545$4,994 
9/24/2019$2,636$7,630 
2/7/2020$1,737$1,737Usage begins again at 0 on 2/1/20
2/19/2020$128$1,865 
2/20/2020$128$1,993