Help with DAX

[salamekhamis]

I need help creating a measure for Total Amonut such that If I filter date by month for a certain loan and the total amount value is blank that month then I should get the value for the last nonblank month as shown:

I should see this for December:

Please help with the measure. I used this for Total Amount
Total Amount=Calculate(sum('loans'[Amount]))

[andrewsommer · Accepted solution]

Try this:

Total Amount = 
VAR CurrentMonthAmount = SELECTEDVALUE('Table'[Amount])
VAR LastValidAmount = 
    CALCULATE(
        MAX('Table'[Amount]),
        'Table'[Amount] <> BLANK(),
        'Table'[Date] < MAX('Table'[Date])
    )
RETURN 
    IF(ISBLANK(CurrentMonthAmount), LastValidAmount, CurrentMonthAmount)

Please mark this post as solution if it helps you. Appreciate Kudos.

[v-ssriganesh]

Hi @salamekhamis,
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-ssriganesh]

Hi @salamekhamis,

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-ssriganesh · Accepted solution]

Hi @salamekhamis,
Thank you for bringing your query to the Microsoft Fabric Community Forum.

I have reproduced your scenario using my sample data, and I am sharing the steps, expected output screenshot and PBIX file for your reference.

• Created a Date Table

DateTable =

ADDCOLUMNS (

    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ),

    "MonthName", FORMAT ( [Date], "MMMM" )

)

Created a relationship between LoanData[Date] and DateTable[Date].

• Created the Measure for Last Non-Blank Amount

LastNonBlankAmount = 
VAR LastAmount =
    CALCULATE(
        SUM(LoanData[Amount]),
        FILTER(
            ALL(LoanData),
            LoanData[Loan No] = SELECTEDVALUE(LoanData[Loan No]) &&
            LoanData[Date] <= MAX(LoanData[Date]) &&
            NOT(ISBLANK(LoanData[Amount])) 
        )
    )
RETURN LastAmount

This measure ensures that if a month has a blank amount, it fetches the last available non-blank value.

• Below is the expected output screenshot:
  

If this is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @salamekhamis,

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 other community members with similar problems can find a solution faster.
Thank you.

[andrewsommer · Accepted solution]

Try this:

Total Amount = 
VAR CurrentMonthAmount = SELECTEDVALUE('Table'[Amount])
VAR LastValidAmount = 
    CALCULATE(
        MAX('Table'[Amount]),
        'Table'[Amount] <> BLANK(),
        'Table'[Date] < MAX('Table'[Date])
    )
RETURN 
    IF(ISBLANK(CurrentMonthAmount), LastValidAmount, CurrentMonthAmount)

Please mark this post as solution if it helps you. Appreciate Kudos.