Calculating using Future Quarters values

[jpbi23]

I have a table called MasterAll where I have built these 3 measures, 

I have a matrix visual that shows these 3 measures by fiscal quarters. I want to write another measure (FinalTotal) that would take, 

[Booked / Gross Retained $] - [Out-Qtr Late Booked $] + [Out-Qtr LateBooked $]--> value from the next quarter

For example, in 2023-Q1, it would be something like this, 

[60,347,535] - [3,209,341] + [1,026,289] --> grabbing 2023-Q2 value. Always should get this value from the next quarter

For another example, in 2023-Q2, it would be something like this, 

[72,782,964] - [1,206,289] + [2,620,736] -- grabbing 2023-Q3 value. 

is there a way to write this?

Thanks,

Jay

[v-jtian-msft]

Hi,@jpbi23 .I am glad to help you.
According to the description, you want to add a row of MEASUREMENT to the matrix [FinalTotal], there are two limitations to your problem
1. The dimension fields in the matrix must be real fields.
Suppose your data is structured as follows:

Then you need to make sure that the data category in the [Attribute] column contains the category “FinalTotal” (actually this is just the name of the measure, it does not exist in your real data).
This limitation you can try to achieve by customizing the matrix header (the second issue is similar to your needs).
But note that this way is static, if in your real data, the categories are increased, you need to recreate the matrix header
issue01:Solved: How to show a Row Total as Row element in a matrix... - Microsoft Fabric Community

issue02:Solved: Matrix Overlapping Groups - Microsoft Fabric Community

issue03: Solved: How to add a custom measure at the end of matrix v... - Microsoft Fabric Community

For the second difficulty:
You need to get and use the data of the current row of the other columns in the current column in the matrix, and hopefully not be affected by the matrix dimension, which is harder to achieve in Dax.
I think a better approach would be to modify your data itself by pivoting or inverse pivoting (using M code to modify the data source structure directly) and try adding [Attribute]=“FinalTotal” to the original data.
If your measure calculation is too complex, or if for some reason you cannot modify the data structure.
You can try to create a calculated table, add “FinalTotal” to the categorized data, calculate the actual data and show the final result in a matrix (instead of just using the measure to achieve what you want).

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.