CODEWITHSUNDEEP
powerbidax
Jess8766
Jess8766

Reputation: 417

Power BI - calculate % paid vs expected?

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

Still learning DAX so apologies if I am overcomplicating the below..

enter image description here

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?

Updated enter image description here

Upvotes: 0

Views: 131

Answers (1)

intruderr
intruderr

Reputation: 353

I've put a sample of your data into the model, and it looks as follows

Schedule table

enter image description here

Payment table

enter image description here

Add the relation between two tables on ContractId.

Then add measures as follows (remember to change the column names respectively):

Payment Amount = SUM( AmountPaid[PaymentAmt] )

Expected Amount = SUM( Schedule[ExpAmnt] )

Expected Amount Running Total = 
VAR maxPD = MAX( AmountPaid[PaymentDate] )
VAR result = 
    CALCULATE(
        [Expected Amount],
        FILTER(
            Schedule,
            Schedule[ScheduleDate] <= maxPD
        )
    )
RETURN 
    result

Payment Amount Running Total = 
VAR maxPD = MAX( AmountPaid[PaymentDate] )
VAR minPD = 
    CALCULATE( 
        MIN( AmountPaid[PaymentDate] ),
        ALLSELECTED( AmountPaid )
    )
VAR result =
    CALCULATE(
        [Payment Amount],
        ALL( AmountPaid[PaymentDate] ),
        AmountPaid[PaymentDate] >= minPD,
        AmountPaid[PaymentDate] <= maxPD
    )
RETURN
    result

ALL and ALLSELECTED functions allow you to add a PaymentDate slicer to your report. The Payment Amount Running Total will be computing dynamically based on that date range selection.

% of expected Payment gives you the percentage.

% of expected Payment = 
    DIVIDE( [Payment Amount Running Total], [Expected Amount Running Total] )

The final result is:

enter image description here

Note that I am using my native language in the report. So Rok is Year and Miesiąc is Month. Months are as follows Oct, Feb, May, Jun, Aug, Jan, Jul.

I hope you find it helpful, and it works for you.

Edit:

Add REMOVEFILTERS to Expected Amount Running Total. In your case, some ContractIds have probably no related row in Schedule table. The BLANKS are occurs then. To prevent that kind of situation, you have to change the context via CALCULATE by removing all filters from the main table.

Expected Amount Running Total = 
VAR maxPD = MAX( AmountPaid[PaymentDate] )
VAR result = 
    CALCULATE(
        [Expected Amount],
        REMOVEFILTERS(AmountPaid),
        Schedule[ScheduleDate] <= maxPD
    )
RETURN 
    result

I've added some additional rows to my sample dataset to AmountPaid table only. See the outputs below. The first table is without REMOVEFILTERS part. enter image description here

Upvotes: 1

Related Questions