Forum Discussion

salame's avatar
salame
New Member
1 year ago
Solved

help with dax

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 Amou...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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's avatar
MFelix
Super User
1 year ago

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's avatar
salamekhamis
Regular Visitor
1 year ago

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