Help requested on Advanced DAX Problem

[DAXStudent]

I have an advanced DAX problem that I need help with. I have 2 tables, Calendar and Transactions. The Calendar has a dynamic column named Relative Month that returns 0 for all dates in this month, -1 for all dates in last month, etc.

The transactions table looks like this:

Invoice ID	Date of Service	Transaction Date	Amount	Amount Type	DOSMonthDiff
1234	1/1/2024	1/1/2024	100	Invoice	0
1234	1/1/2024	2/15/2024	50	Payment	-1
1234	1/1/2024	3/15/2024	25	Payment	-2
1234	1/1/2024	5/24/2024	25	Payment	-4
1235	2/1/2024	2/1/2024	100	Invoice	0
1235	2/1/2024	3/14/2024	50	Payment	-1
1235	2/1/2024	5/31/2024	50	Payment	-3
1236	3/1/2024	3/1/2024	100	Invoice	0
1236	3/1/2024	3/15/2024	50	Payment	0
1236	3/1/2024	7/1/2024	50	Payment	-4
1237	4/1/2024	4/15/2024	100	Invoice	0
1237	4/1/2024	8/15/2024	50	Payment	-4
1237	4/1/2024	12/15/2024	50	Payment	-8
2234	3/1/2025	3/1/2025	100	Invoice	0
2234	3/1/2025	4/15/2025	50	Payment	-1
2234	3/1/2025	5/15/2025	25	Payment	-2
2235	4/1/2025	4/1/2025	100	Invoice	0
2235	4/1/2025	5/15/2025	50	Payment	-1
2236	5/1/2025	5/1/2025	100	Invoice	0
2236	5/1/2025	5/15/2025	50	Payment	0
2237	6/1/2025	6/1/2025	100	Invoice	0

As you can see Invoices and Payments are stacked vertically. DOSMonthDiff is the difference between the Transaction Date Month and the Date of Service Month.

The business wants me to summarize the data in 2 different views and then join the two dynamically.

First I need to pivot Payments and Invoices for Last Year by DOSMonthDiff with extra calcs, like this (done):

Next I need to pivot this year's data by Month and relative month like this (done):

Lastly I need to put the two together, with the measure "Payment Amount Divided by Whole Year Total - Last Year" joining DOSMonthDiff to Relative Month. This is what the solution should look like:

YearMonth	Relative Month	Invoiced Amount	Payment Amount	Paymet Amount Divided by Whole Year Total - Last Year
Jun-25	0	100		12.5%
May-25	-1	100	50	25.0%
Apr-25	-2	100	50	6.3%
Mar-25	-3	100	75	12.5%

I must keep the current relationship between the calendar and the Transaction Table. The solution must be a DAX solution, not a Power Query solution. However you can introduce a new table(s) if needed. Ultimately the solution needs to be dynamic where "Payment Amount Divided by Whole Year Total - Last Year" always represents the year prior to the current year and where DOSMonthDiff is joined to Relative Month … even as time goes on, years change, relative month changes.

Download my pbix here:

https://www.dropbox.com/scl/fi/fdy5zodytrxz53hzb09mq/20250606-Advanced-DAX-Problem.pbix?rlkey=wvg26l...

[v-sdhruv]

Hi @DAXStudent ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @DAXStudent ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[v-sdhruv]

Hi @DAXStudent ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution  so other members can easily find it.
Thank You

[Rupak_bi · Accepted solution]

Hi @DAXStudent ,

Here is your solution.

Steps:
1. Create a calculated column inthe transaction table

DOS-Relative Month Key = 
var Yr = year(Transactions[Date of Service])+1
var Mo = month(today())+Transactions[DOSMonthDiff]
Return
date(yr,mo,1)

2. Make an inactive relationship between the new column and calender date

3. Now Make a new Measure to use in the matrix

Payment Amount Divided by Whole Year Total - Last Year (New) = 

Var Current_Payment = CALCULATE([Payment Amount],USERELATIONSHIP('Calendar'[Date],Transactions[DOS-Relative Month Key]))
Var Total_Payment = CALCULATE([Payment Amount],ALL('Calendar'),'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year])-1)

RETURN

CALCULATE(Current_Payment/Total_Payment,USERELATIONSHIP('Calendar'[Date],Transactions[DOS-Relative Month Key]))

Now make the table to get the desired result. 

Hope this will solve the purpose. If you want something more specific, let me know

Else Plz accept as solution.....