Forum Discussion
Help requested on Advanced DAX Problem
- 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.....
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.....