Forum Discussion
Interest Calculation
Hi anwargabr ,
Actually I changed my mind. It seems "at least to me" that it is easier to unpivot the data using dax. I explained earlier how to create a simple date table in DAX. Now all you need to do is to create a new table by CROSSJOIN both Date and Loans tables to generate all combinations then filter down only to the relivant ones as per below code.FullData = VAR FullDateData = CROSSJOIN ( 'Date', Loans ) VAR ExistingDateData = FILTER ( FullDateData, [Date] > [From] && [Date] <= [to] ) RETURN ExistingDateDataThis is how the original data looks like
an this the how the unpivoted data looks like
Next you create the relationship between Date and FullData tables.
Last you need to create your measureInterest Amount = SUMX ( FullData, DIVIDE ( FullData[Amount] * FullData[Rate], 360, 0) )The results 100% matchews the calculations in your excel sheet.
Here is the link to download the Pbi file https://www.dropbox.com/t/axD7brB2Czc8br8V
Good Moring anwargabr
Check the file here https://www.dropbox.com/t/poTbyLxMZFOPDbpS
Not sure if I understand your equation or how you calculated the monthly interest in this table. However, if you need to slice by Loan number then you need to create a measure as calculated columns are no evaluated in the filter context rather in the row context of the iterated table.
To filter the matrix by loan number you can either use the slicer or just click on the chart column. Also you can slice the chart by month by clicking on the month name in the matrix. Power Bi provides interactive visuals that can filter each other.
If you have any question regarding the four measures and the two calculated columns in the file, please feel free to let me know. If you find my input hepful and fulfills your requirements, please kudo and mak as Accepted Solution.
Thanks and have agreat day
Dear tamerj1
Thanks for my late reply due to the reason I've explained to you in the private message.
I've attached both excel and Pbi files here
https://www.dropbox.com/s/hcqqplt4b19sn3a/Test%20Ditribute%20days.rar?dl=0
the interest fromua and the manual calcaultion for days are all in the excel sheet.
Thanks and Best Regards.
Thank you anwargabr
Now it is clear. It can be done with totally with DAX code but this is not the standard method. As long as you are making a transformation in your work you better follow a professional standard in your data. DAX in either power Bi or Power Pivot likes to deal with Columnar unpivoted data. That means less columns more rows. In your example that means you need to have the inrest rate and the loan ammount for each bank/loan type for each date date. That would generate a table of more than 1000 rows. This is a an extremely small number of rows compared to what DAX can handle (milions of rows at a time). You can use either excel or power pivot to do that. I can help you out on this but it could be a good excersie for you.
Next you need a proper Date table which I will help you with it. The rest is a peice of cake.
Try to unpivot your data and I will do something from side as well. Have a great day!Hi anwargabr ,
Actually I changed my mind. It seems "at least to me" that it is easier to unpivot the data using dax. I explained earlier how to create a simple date table in DAX. Now all you need to do is to create a new table by CROSSJOIN both Date and Loans tables to generate all combinations then filter down only to the relivant ones as per below code.FullData = VAR FullDateData = CROSSJOIN ( 'Date', Loans ) VAR ExistingDateData = FILTER ( FullDateData, [Date] > [From] && [Date] <= [to] ) RETURN ExistingDateDataThis is how the original data looks like
an this the how the unpivoted data looks like
Next you create the relationship between Date and FullData tables.
Last you need to create your measureInterest Amount = SUMX ( FullData, DIVIDE ( FullData[Amount] * FullData[Rate], 360, 0) )The results 100% matchews the calculations in your excel sheet.
Here is the link to download the Pbi file https://www.dropbox.com/t/axD7brB2Czc8br8V