Projected revenue based on maturity of the credit installments

[arinyc]

The table below shows a list of examples with their purchases on credit with an approved number of instalments. The total purchase amount is displayed in the "Amount" column. I wish to divide the total amount by the number of approved instalments, in order to calculate the monthly payments associated with the corresponding future months, starting from the month when the invoice was issued. This will enable me to forecast expected income by selecting a specific month and year in the future, using a date filter.

This is an example of the table with original data:

The next table is a the result I want to get - overview of the expected income based on the maturity of the installment. Does anyone know how to write a DAX expression that calculates this?

Thanks in advance.

[amitchandak · Accepted solution]

@arinyc , I usually add end date to the table first and create a table or use measure(with help from the date table

End Date = Date(year([Date]), month([Date]) + [No of Installment] , day([Date]) )

A measure with help from disconnected date table, having month year column in the date table

Current Month = CALCULATE(Sumx(FILTER('Table','Table'[Date]<=max('Table'[Date]) && (ISBLANK('Table'[End Date]) || 'Table'[End Date]>max('Table'[Date]))),('Table'[Amount])))

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[v-yifanw-msft · Accepted solution]

Hi @arinyc ,
You can try @amitchandak 's method first, meanwhile I created another way to solve your problem, you can refer to the following steps:

1.Add an index column and other new columns.

Amonut every month = 'Table'[Amount]/'Table'[Number of payments(Installments)]

allmonth = MONTH('Table'[Date])+'Table'[Number of payments(Installments)]-1

datemonth = MONTH('Table'[Date])

 2.Add a new datetable.

DateTable = 
VAR _1 =
    MIN ( 'Table'[Date] )
VAR _2 =
    _1
        + ( MAX ( 'Table'[allmonth] ) * 30 )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _1, _2 ),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "Month_",
            IF ( YEAR ( [Date] ) = 2024, MONTH ( [Date] ) + 12, MONTH ( [Date] ) )
    )

3.Add a measure and put into Matrix.

Measure = 
VAR _1 = IF (
    SELECTEDVALUE('DateTable'[Month_])
        >= CALCULATE ( MAX ( 'Table'[datemonth] ), FILTER ( 'Table', 'Table'[Index] = 1 ) )
        && SELECTEDVALUE('DateTable'[Month_])
            <= CALCULATE ( MAX ( 'Table'[allmonth] ), 'Table'[Index] = 1 ),
    CALCULATE ( MAX ( 'Table'[Amonut every month] ), FILTER ( 'Table', 'Table'[Index] = 1 ) ),
    BLANK ()
)
VAR _2 = IF (
    SELECTEDVALUE('DateTable'[Month_])
        >= CALCULATE ( MAX ( 'Table'[datemonth] ), FILTER ( 'Table', 'Table'[Index] = 2 ) )
        && SELECTEDVALUE('DateTable'[Month_])
            <= CALCULATE ( MAX ( 'Table'[allmonth] ), 'Table'[Index] = 2 ),
    CALCULATE ( MAX ( 'Table'[Amonut every month] ), FILTER ( 'Table', 'Table'[Index] = 2 ) ),
    BLANK ()
)
VAR _3 = IF (
    SELECTEDVALUE('DateTable'[Month_])
        >= CALCULATE ( MAX ( 'Table'[datemonth] ), FILTER ( 'Table', 'Table'[Index] = 3 ) )
        && SELECTEDVALUE('DateTable'[Month_])
            <= CALCULATE ( MAX ( 'Table'[allmonth] ), 'Table'[Index] = 3 ),
    CALCULATE ( MAX ( 'Table'[Amonut every month] ), FILTER ( 'Table', 'Table'[Index] = 3 ) ),
    BLANK ()
)
VAR _4 = IF (
    SELECTEDVALUE('DateTable'[Month_])
        >= CALCULATE ( MAX ( 'Table'[datemonth] ), FILTER ( 'Table', 'Table'[Index] = 4 ) )
        && SELECTEDVALUE('DateTable'[Month_])
            <= CALCULATE ( MAX ( 'Table'[allmonth] ), 'Table'[Index] = 4 ),
    CALCULATE ( MAX ( 'Table'[Amonut every month] ), FILTER ( 'Table', 'Table'[Index] = 4 ) ),
    BLANK ()
)
RETURN
_1+_2+_3+_4

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[arinyc]

The solution provided functions seamlessly, but is it possible to make it adaptable to varying numbers of rows in the table? In the aforementioned example, there are 4 lines, indexed from 1 to 4. However, if additional lines are added to the table, I would need to modify the DAX expression by introducing new variables for the newly indexed rows. The question then arises: can this solution dynamically accommodate a table with n rows?

[arinyc]

@v-yifanw-msft  It works like a charm, briliant solution. Thank you for detailed observations.

[amitchandak · Accepted solution]

@arinyc , I usually add end date to the table first and create a table or use measure(with help from the date table

End Date = Date(year([Date]), month([Date]) + [No of Installment] , day([Date]) )

A measure with help from disconnected date table, having month year column in the date table

Current Month = CALCULATE(Sumx(FILTER('Table','Table'[Date]<=max('Table'[Date]) && (ISBLANK('Table'[End Date]) || 'Table'[End Date]>max('Table'[Date]))),('Table'[Amount])))

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[arinyc]

Hi @amitchandak ,

I tried your solution but looking at your code provided above I didn't get what you mean by "A measure with help from disconnected date table" (how to implement that).

I created 'End Date' column in my table first:

End Date = DATE(YEAR('Table'[Date]), MONTH('Table'[Date]) + 'Table'[Number of payments(Installments)], DAY('Table'[Date]))

Then I created a measure 'Current Month' in the table:

Current Month = CALCULATE(SUMX(FILTER('Table', 'Table'[Date] <= MAX('Table'[Date]) && (ISBLANK('Table'[End Date]) || 'Table'[End Date] > MAX('Table'[Date]))), 'Table'[Amount]))

The result I got isn't what I expected.

This is my Power BI file.