Forum Discussion
Visualization for contract start/end dates and cost
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_MathurI've tried three times now and powerbi community is not cooperating.
Please see image I posted previously. Here is an example of contract cap data.
| BPID | Customer Name (BPID) | contr_num | SAP Contract Start | SAP Contract End | Contract Cap |
| 100769396399 | ABC COMPANY (100769396399) | 8003656409 | 2/1/2018 | 1/31/2019 | $8,000 |
| 100769396399 | ABC COMPANY (100769396399) | 8004081579 | 2/1/2019 | 1/31/2020 | $10,000 |
| 100769396399 | ABC COMPANY (100769396399) | 8004575589 | 2/1/2020 | 1/31/2021 | $9,000 |
Hi,
The visual that you want to create is immaterial. First and foremost, we have to calculated the correct figures in a simple Table format. So share your input Tables and show the exact expected result in a simple Table format.
Ashish_MathurI'm trying to provide as you ask, and if this isn't it, I don't know what else to do. I'm trying to figure out how to sum the List price cumulatively for the list price transactions that occur between the start and end date range for each contract. In other words, the list prices accumulate from the start to the end of each contract. When the contract is over, the accumulation starts over again at 0. Any help in how to do this is greatly appreciated. Thank you.
CONTRACT INPUTS BPID Customer Name (BPID) contract_num SAP Contract Start SAP Contract End Contract Cap 399 ABC COMPANY (399) 8003656409 2/1/2018 1/31/2019 $ 8,000.00 399 ABC COMPANY (399) 8004081579 2/1/2019 1/31/2020 $ 10,000.00 399 ABC COMPANY (399) 8004575589 2/1/2020 1/31/2021 $ 9,000.00 DATA INPUTS DATA INPUTS DESIRED RESULT Order Submit Date 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 $2,592 $2,592 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/1/2019 $0 Usage begins at 0 again on 2/1/19, with a new contract 2/25/2019 $2,940 $2,940 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/1/2020 $0 Usage begins again at 0 on 2/1/20 2/7/2020 $1,737 $1,737 2/19/2020 $128 $1,865 2/20/2020 $128 $1,993 Hi,
In the second table, assuming the first 2 columns are user inputs, there should be 2 columns there as aadditional user inputs - BPID and contract_num. Why are they absent?
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.