Forum Discussion
DAXStudent
1 year agoNew Member
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...
- 1 year ago
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.....
v-sdhruv
1 year agoCommunity Support
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