cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Projected revenue based on maturity of the credit installments

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?

2 ACCEPTED SOLUTIONS
Super User

@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

Community Support

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])``

``````DateTable =
VAR _1 =
MIN ( 'Table'[Date] )
VAR _2 =
_1
+ ( MAX ( 'Table'[allmonth] ) * 30 )
RETURN
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:

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

Best Regards,

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

5 REPLIES 5
Community Support

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])``

``````DateTable =
VAR _1 =
MIN ( 'Table'[Date] )
VAR _2 =
_1
+ ( MAX ( 'Table'[allmonth] ) * 30 )
RETURN
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:

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

Best Regards,

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

Frequent Visitor

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?

Frequent Visitor

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

Super User

@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

Frequent Visitor

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors