Hi Everyone, hope you are all safe during this unprecedented times. I am back for some assistance and I will much appreciate if you can help me out :).
I have created a Power BI (PBI) dashboard to report on contracts and variations. I have three tables in my PBI: Dates, Contracts Register & Contracts Variations.
I had to create number of reports which I have but the only one I am having issues is this one which is called “Contracts and Variations Quarterly Report”. Basically, this reports all the new contracts executed and new variations raised in the last quarter. So the report should look like this:
Note: this is sample report only for discussion purposes and I have attached a sample excel file that shows contracts table, variations table and quarterly report table. Here is snapshot of the contracts table and variations table:
Quarterly Report.xlsx (11.4 KB)
I have developed about 85% of this report but I am having issues creating couple of columns and that’s where I need your expertise. This report uses “Variation Date” column to generate the report. The issue I am having are creating the following columns in he Quarterly report:
- New Contract Let Value - this column returns any new contracts executed last quarter and this comes from the Contracts Table.
- Variations Value - this column returns sum of all variations value executed last quarter for a contract and comes from variations Table.
- Prior Variations Value - this column returns sum of all variations executed prior to last quarter and comes from variations Table.
Ultimately the “Current Approved Value” (Last column in the report) = Original Value + Variations Last Quarter + Prior Variations
When I mean last quarter I mean (Jan 2020 - March 2020). I am from Australia so our financial year starts 1 July.
Hope that provide enough information and thank you in advance for your help.