Forum Discussion
Help requested on Advanced DAX Problem
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:
Hi DAXStudent ,
Here is your solution.
Steps:
1. Create a calculated column inthe transaction tableDOS-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.....
4 Replies
Hi DAXStudent ,
Here is your solution.
Steps:
1. Create a calculated column inthe transaction tableDOS-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.....
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 YouHi @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 YouHi @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
