Forum Discussion
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 | 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 |
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.
8 Replies
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?
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
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.
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.
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.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.
