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 :).
Context:
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.
Solution:
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:
Problem:
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.
Here’s a possible solution (at least it’s calculations match your sample).
calculate measures for last quarter first and last dates
First Date of Last Quarter =
VAR TodaysDate = TODAY()
RETURN
CALCULATE(
MIN( Dates[Date] ),
PREVIOUSQUARTER( CALCULATETABLE( VALUES( Dates[Date] ), Dates[Date] = TodaysDate)
)
)
{{ for last date use MAX( Dates[Date] ) }}
2) calculate measure for new contract let value
New Contract Let Value =
CALCULATE( SUM( Contracts[Original Value] ),
FILTER( Contracts,
Contracts[Execution Date] >= [First Date of Last Quarter] &&
Contracts[Execution Date] <= [Last Date of Last Quarter]
)
)
3) calculate measures for variation last quarter and before last quarter
Variation Last Quarter =
CALCULATE( SUM( Variations[Variation Value] ),
FILTER( Variations,
Variations[Variation Date] >= [First Date of Last Quarter] &&
Variations[Variation Date] <= [Last Date of Last Quarter]
)
)
sum 2 & 3 for approved value
add [Last Quarter and Before] calculated column to date table and use as report filter to exclude contracts in current quarter
Last Quarter and Before = IF( Dates[Date] <= [Last Date of Last Quarter], 1, 0 )
Hi @tt.xavier, we’ve noticed that no response has been received from you since the 3rd of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi @tt.xavier, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
I have implemented your solution and it works. However, I have one issue and that is the relationship between the contracts table and variations table. It is not allowing me to do a 1-to-many relationship and it only allows many-to-many. So all your measures works independently but some measures don’t work when I bring it into the table together and I think it’s due to the relationship issue. Why am I having the relationship issue? Thank you.
