Summary Table

[Anonymous]

Hi,

I have a table with Columns LoanID, Status, StatusID, LoanRemaining Balance, Accrual Date, Days Past Due which has records for every date. I want to have a summarized table having the max accrual date in each month and the year and its corresponding days past due and loan remaining balance for loans having status id as 100 and 1001

Below is where you can see the dataset and the result table,

https://drive.google.com/drive/folders/1kUJEzxtkH40lzd3h4FvOuysf9JPo8_FS?usp=sharing

The sql query used is,

select loan_id, loan_status, status_id,loan_remaining_balance, max(accrual_date) as maxdate, days_past_due from accrualtable
where
status_id in (100, 1001) 
group by loan_id, year(accrual_date), month(accrual_date)
order by maxdate, status_id;

Please Help me write DAX query for it!

@Ashish_Mathur 

Thanks,

Dharani

[Anonymous]

Thank You so much Sir

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

In the Dataset worksheet, the entry in the Days past due column in row 5 is -28  In the Result worksheet, then entry in the  Days past due column in row 2 is -31.  Why this discrepancy?

[Anonymous]

Sorry sir my mistake. I guess my query is wrong, the result worksheet in row 2 must have -28

[Ashish_Mathur · Accepted solution]

All the answers on your result worksheet are wrong.  Download my PBI solution file from here.

Hope this helps.