help with dax

[salame]

I have this dataset: I need to create a sum amount and filter by month such that if the value is blank in that month I get the value of the last previous month that was nonblank.

Loan No	Amount	Date
LNA-003	31849315.36	01/01/2025
LNA-004	19109589.16	12/10/2025
LNA-006	31849315.36	11/11/2025
LNA-009	6369863.25	20/11/2025

The result for LNA-009 per month to be as shown with December not blank but picks value for November:

Loan No	Amount	month
LNA-009	6369863.25	November
LNA-009	6369863.25	December

[v-karpurapud]

Hi @salame 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @salame 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @salame 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud · Accepted solution]

Hi @salame 
Thank you for reaching out to Microsoft Fabric Community Forum.

Try with below DAX:

Amount_Latest = 
VAR LastAmount = 
    CALCULATE(
        SUM('loan collaterals'[Outstanding Amount]),
        FILTER(
            ALL('Ultimate Calendar'),
            'Ultimate Calendar'[Year] = MAX('Ultimate Calendar'[Year]) &&
            'Ultimate Calendar'[Month] <= MAX('Ultimate Calendar'[Month])
        )
    )
RETURN
    IF(
        ISBLANK(SUM('loan collaterals'[Outstanding Amount])),
        LastAmount,
        SUM('loan collaterals'[Outstanding Amount])
    )

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you.

[MFelix]

Hi @salame ,

To what I can understand you want to create a line for each month for each loan is that correct?

A couple of questions on this:

• Do you want to have this indefinetly for each loan? Is there a end date for each one?
• Is the amount always the same for all the lines or do your want to do a part of the amount you present on the table?

[salame]

line for each month for each loan is that correct?-this is correct.
yes analysis is done say for a certain year and month...amount should always be nonblack where there has been transaction(amount exist) in that month or not.

Is the amount always the same for all the lines or do your want to do a part of the amount you present on the table?-that was just an example''there are different amounts for each loan per month. For those months that amount is blank for a certain loan...we should always pick previous value for a month that was nonblank

[MFelix]

Hi @salame ,

Based on the information you have sent can you please send a more complete example so I can get the correct syntax for you.

[salamekhamis]

I have two tables 
Loan Register(dimension table) linked to loan Collaterals(fact) on Loan No

Loan collateral table has columns collateral Code(unique identifier)|Loan No|Oustanding Amount|Oustanding loan balance date which are posted monthly.

Loan Collateral table is linked to Ultimate Calendar date on Oustanding loan balance date.
I want to analyse Loan No and see Outstanding Amount per month. If outstanding amount is blank in a month like December since there is no transaction for that loan that month..I should see the last nonblank value for a previous month like November.

This is what I have:

Since transaction only happened in May for Loan-092 I will have that value in May.When we go to June,there is no transaction in June so the value is blank. I need a measure to get the previous value in a month that was nonblank for June..also July it should read the last nonblank month value.
 I got Amount = Calculate(sum('loan collaterals'[Amount])) which can also be found using a direct column in the table loan collaterals