Forum Discussion

DAXStudent's avatar
DAXStudent
New Member
1 year ago
Solved

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...
  • Rupak_bi's avatar
    1 year ago

    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.....