Relate measure from table B back to table A based on different date column

[jon11w]

Hi,

EDIT: a simplified representation of the problem in a pbix file https://wetransfer.com/downloads/f77f2ef3c54465c43fefc425e7bb7c5d20200512142519/8f2de5

I'm not 100% sure how to summarise this issue, but I've been stuck on it for days so thought I would reach out to the community. Unfortunately i cannot share the data or model itself, but I have included screenshots where I can.

I have a table, Summary - CT Repayments, which calculates (as a measure) a total amount repayable per year. My year column is called "FY ended March." I have a "Number of repayment years" column, and then two measures; "First repayment year" and "Final repayment year." Finally, I have a measure that calculates annual repayment; total amount repayable / number of repayments years.

I have a second table, Summary - Repayment schedule, which contains one row per possible future repayment year up to 2050, related to the FY ended March column from the first table. This looks like below, where year 2 > FY ended March:

Now i can calculate my annual repayments over future repayment years;

So far so good... I can create a table visualisation plotting Year 2 against my Repayment schedule option 1 measure, and it gives me the expected values summarised against the correct years. My issue is that i now need to add this repayment schedule with measures calculated against the FY ended March column.

When plotting my Repayment schedule measure against FY ended March, it summarises differently, which is to be expected since the measure is calculated against future years rather than current year. To get around this I have tried all manner of things, mostly around the data model. What i have ended up with doesn't work, but it does make logical sense to me (from a relational database perspective, which is clearly not how i should be approaching this) and is simple to explain, so i will post it to explain my thinking.

I have created an inactive relationship between my Summary - CT repayments and Summary - Repayments scheudle tables, joining FY ended March from the first table to Year 2 from the second;

I then create a measure in my CT repayments table to try and utilise this inactive relationship. In my head, the aim of this is to join on Year 2 in order to be able to summarise my "Repayment schedule" measure against "FY ended March."

As i say, this makes sense in a relational database sense, but i'm struggling here with the context of my measures, as this simply returns 0 for each year. Interestingly, if i change the Year 2 here to 'Summary - Repayments'[FY ended March] i get the same result as summarising i was before.

I'm not sure if this makes sense, but i've tried everything so any help would be greatly appreciated! This is my first pot so if any body could recommend any labels or tags that would be helpful.

Cheers!

[Anonymous]

Hi there.

OK. I admit I've got problems following your train of thoughts and understanding the issue... Would you mind creating a small model with some fake but realistic data in a pbix file and post a link to the file here? This can even be a simplified model but it has to have all the necessary and important ingredients. If there can be some unusual data, it has to be accounted for as well.

Also, normally when you want help you should give an example of the initial state and the expected state (even if it's a fake table in Excel) created manually, adding explanations where needed to make things crystal clear.

You should also explain in detail the mechanics of the measures. Please note that you've been working with this model for some time and each one of us has no foggy idea about what it is you're doing and want to achieve. This is why good explanations are indispensable.

Thanks.

Best
D

[jon11w]

Thanks, I think i struggled explaining because i struggle to understand the problem myself. As you suggested, i have created the attached simplified example, containing two tables.

Example file: https://wetransfer.com/downloads/f77f2ef3c54465c43fefc425e7bb7c5d20200512142519/8f2de5

In my Summary - CT repayments, i have the following. Essentially i have a series of loans which i need to spread the vaklue of over the term that the will be repaid (i.e., between the start and end date). In the example, "Total repayment" is a column, whereas in my actual model this is a measure, calculated from measures in other tables (connected via FY ended March). "First repayment year", "Final repayment year" and "Annual repayment" are all measures, although the first/final measures could (should?) probably be converted to columns.

The second table is derived from the FY ended March column in the first table, cross joining against itself to create a row for every combination of dates. I then filter out any rows where the second date column is less than the FY ended March column, to give me all future years within which an annual repayment could be applied for each loan.

Finally the measure "Annual repayment schedule" applies the annual repayment from the first table to all future years within the first and final repayment year range.

My issue is depicted in the image below. The left-hand table shows what the sum of phased payments should look like, plotting the "Annual repayment schedule" measure against the Year 2 column from my second table. The right-hand side shows what the phasing looks like when plotting the measure against the original FY ended March column from my first table.

If you have any insight I would be extremely greatful. Reducing the issue down to this simple replication has helped me a bit. It feels like there is a simple solution, however i'm not sure whether it is a DAX  solution (see my "Attempt at fixing" measure" or a data model issue. Is there anything that you need more information on?

Thanks for your time!

[Anonymous]

Let me ponder over this a bit... I'll be back soon.

Best
D

[jon11w]

Much appreciated, let me know if there's anything that doesn't make sense!

[Anonymous]

There are many different ways to solve this. I didn't want to multiply tables, so this solution is a minimal one that could be not fast enough if many loans are entered in the Loans table. But with a moderate number of them, it might be good enough.

Please find a file attached.

Best

D