Creating amortization schedule for prepayments, accrual and deferred revenue

[DataNinja777]

Hi Power BI Community,

I am trying to create amortization schedule for service table like below:

In order to do that disconnected tables were set up in the data model.  

Amortization:=SUMX (
    Service,
    IF (
        Service[Start date] <= MAX ( 'Calendar_445'[Date] )
            && Service[End date] >= MAX ( 'Calendar_445'[Date] ),
        Service[Daily amort]
            * ( MAX ( 'Calendar_445'[Date] ) - Service[Start date] ),
        BLANK ()
    )
)

The formula above produced the colored conditionally formatted result to accumulate the expenses or revenues throughout the service performance period.  I'd like to write the formula to calculate the P&L charge for the respective months, which respects the time dimension and add up correctly by day granurality in the calendar table, but due to the lack of relationship between the calendar table and the service table which contain two date field, I found it difficult to write the formula to add up the daily amount cumulating over time in the calendar table.  

I'd appreciate it if anyone could write the formula to show the max month - previous max month of the outputs shown in the conditional formatting above.  

PL charge :=
VAR PreviousMonthAmortization =
    CALCULATE (
        [Amortization],
        ALL ( Calendar_445 ),
        KEEPFILTERS ( 'Calendar_445'[Fiscal Month Number] - 1 )
    )
RETURN
    [Amortization] - PreviousMonthAmortization

I've written the formula above, but it is not producing the intended output and just producing the same output as the visual above.  

Thank you for your help.  

[Ashish_Mathur]

Hi,

Based on data shared in the first image, show the expected result very clearly.  Also, share data in a format that can be pasted in an Excel file.

[DataNinja777]

Thanks @Ashish_Mathur 

Please find attached the table in copy and pastable format.  

Customer	Start date	End date	Amount	Period	Daily amort
Customer 1	01/01/22	12/31/23	1000000	729	1371.742112
Customer 2	03/01/22	02/28/27	1300000	1825	712.3287671
Customer 3	05/01/24	04/30/28	1430000	1460	979.4520548
Customer 4	07/03/21	06/30/23	890000	727	1224.209078
Customer 5	08/25/22	07/31/27	1157000	1801	642.4208773
Customer 6	03/05/21	02/28/25	1272700	1456	874.1071429
Customer 7	03/08/24	02/28/26	1654510	722	2291.565097
Customer 8	11/02/21	10/31/26	2150863	1824	1179.201206

Expected output is shown in column D and the formulatext for that is shown in column D.  

Thanks a lot for your help.  

[Ashish_Mathur]

I am not sure how much i can help but i can try.  Share the download link of the Excel file with your Pivot Tables and formulas intact.  i will try to translate those formulas in the DAX formula language.

[DataNinja777]

Thanks a lot @Ashish_Mathur , but unfortunately, I cannot share the link.  

PL charge :=
VAR PreviousMonthAmortization =
    MAX ( 'Calendar_445'[Fiscal Year Month Number] ) - 1
RETURN
    [Amortization]
        - CALCULATE (
            [Amortization],
            KEEPFILTERS ( 'Calendar_445'[Fiscal Year Month Number] ) = PreviousMonthAmortization
        )

I'd greatly appreciate it if you could fix the formula above 

"Semantic Error: KEEPFILTERS function can only be used as a top level filter argment of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan."  I am not sure how I can debug the error in the formula.  

[DataNinja777 · Accepted solution]

This issue was resolved by utilizing the dax formula provided in the sqlbi dax pattern training course.