Forum Discussion
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...
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
Hi Ashish_Mathur,
Good point. I'm sorry, I neglected those in the table I provided. It would look like this:
| DATA INPUTS | DATA INPUTS | DATA INPUTS | DATA INPUTS | DESIRED RESULT | |
| Order Submit Date | BPID | Contract_Num | List_Price | Cumulative Usage | Notes |
| 2/1/2018 | 0 | Usage begins at 0 on 2/1/18, which is start of the contract above | |||
| 2/21/2018 | 7693963 | 800365640 | $2,592 | $2,592 | |
| 8/23/2018 | 7693963 | 800365640 | $1,542 | $4,134 | |
| 10/12/2018 | 7693963 | 800365640 | $1,709 | $5,843 | |
| 10/17/2018 | 7693963 | 800365640 | $3,893 | $9,736 | |
| 12/4/2018 | 7693963 | 800365640 | $513 | $10,249 | |
| 2/1/2019 | $0 | Usage begins at 0 again on 2/1/19, with a new contract | |||
| 2/25/2019 | 7693963 | 800408157 | $2,940 | $2,940 | |
| 5/14/2019 | 7693963 | 800408157 | $1,186 | $4,126 | |
| 7/18/2019 | 7693963 | 800408157 | $0 | $4,126 | |
| 8/23/2019 | 7693963 | 800408157 | $323 | $4,449 | |
| 9/2/2019 | 7693963 | 800408157 | $545 | $4,994 | |
| 9/24/2019 | 7693963 | 800408157 | $2,636 | $7,630 | |
| 2/1/2020 | $0 | Usage begins again at 0 on 2/1/20 | |||
| 2/7/2020 | 7693963 | 800457558 | $1,737 | $1,737 | |
| 2/19/2020 | 7693963 | 800457558 | $128 | $1,865 | |
| 2/20/2020 | 7693963 | 800457558 | $128 | $1,993 | |
Thanks for the help.
