Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
arinyc
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:
Capture.PNG

 

 

 

 

 

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?

Capture2.PNG

Thanks in advance.

 

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
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


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

 

 

View solution in original post

v-yifanw-msft
Community Support
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])

 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:

vyifanwmsft_0-1709002765954.png

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.

View solution in original post

5 REPLIES 5
v-yifanw-msft
Community Support
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])

 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:

vyifanwmsft_0-1709002765954.png

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.

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?

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

amitchandak
Super User
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


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

 

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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