Forum Discussion

salamekhamis's avatar
salamekhamis
Regular Visitor
1 year ago
Solved

Help with DAX

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

Need Help
  • andrewsommer's avatar
    andrewsommer
    1 year ago

    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's avatar
    v-ssriganesh
    1 year ago

    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.

5 Replies

  • andrewsommer's avatar
    andrewsommer
    Super User
    1 year ago

    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's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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's avatar
      v-ssriganesh
      Community Support
      1 year ago

      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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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's avatar
    v-ssriganesh
    Community Support
    1 year ago

    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.