Calculate % paid vs expected?

[jd8766]

Hello, wondering if anyone has any ideas how to tackle this one...

I have the below 2 tables in my report (they are both joined in an SSAS model using contractID).

The schedule table tells us for every contract id, how much money we expect to have received at any point in time. So we can see that on the 28th of Jan 2016, we expected to have £233.61 and then on the 1 feb 2019 we expected another £58.4. This needs to be a running total also.. so on the 1 feb 2019 the full total of 292.01 is expected.

The amount paid table below it are all the payments we have had for the contract.. I want to calculate at any point in time what the % payments vs expected was.

So If I was to look at the 1st of feb 2016, I would see a % of 57% as we expected to have 233.61 at that point, and we only had 134.26 (the £200 minus the -65.74)

Ideally I would just be able to see this monthly, so per month year the % of paid vs total expected up to that point.

Does anyone know the best way of achieving this? I am thinking I might need to create an unrelated date table to anchor the measures I will need. But really don't know where I would start with it?

Appreciate any advise on this one..

[V-lianl-msft · Accepted solution]

Hi @jd8766 ,

As far as I know, cannot create a disconnected table in Power BI with Live connection .

Please try to use ALL and ALLEXCEPT function:

Measure = 
var total_ = CALCULATE(SUM(Table2[Amount]),FILTER(ALLEXCEPT(Table2,Table2[ContractID ]),Table2[Payment Date]<=MAX(Table2[Payment Date])))
var schedule_ = CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),Table1[Schedule]<=MAX(Table2[Payment Date])&&Table1[ContractID]=MAX(Table2[ContractID ])))
return divide(total_,schedule_)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[jd8766]

Hi, it's a live SSAS connection so not sure you'd be able to open it?