Forum Discussion

prices35's avatar
prices35
New Member
10 years ago

Referencing Unrelated Data

Hi,   I am a newbie to DAX and I am working on Forecasting and need to reference the "TL APPROVALS" from the previous "Payment_No".  Everything I have tried has resulted in something similar to wha...
samdthompson's avatar
samdthompson
Memorable Member
10 years ago

Looks like you have a join happening on date rather than payment number, hence the PMT approvals summing at a month level rather than a payment level.

Make a calculated column with a related() function to make sure the pmt approval data is coming across properly, and then make the measure based on that (more long winded but you know the join is working by seeing the column). You can then just sumx() to get latest pmt approvals.

prices35's avatar
prices35
New Member
10 years ago

Samdthompson,

 

I am really just trying to reference the response from the previous payment so if you are looking at the row with Payment_No 1 I could use a formula that would say =if(Payment_No 0 approvals = 0, 0, 1).  I have thought about using the approval date and just look back to last month but that doesn't work because Payment_No 0 is a 14 day trial.  That would mean looking back 1 month would give you approvals on Payment_No 0 and some of Payment_No 1.  Any suggestions?

 

Thanks!

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    prices35


    To get the expected output, you can either create a new column in the "Data page" or in the "Edit query page".

    Column = SUMX(FILTER(Sheet1,
	EARLIER(Sheet1[Payment_No])=Sheet1[Payment_No]+1
	&&
	EARLIER(Sheet1[Order Co DATE (Year)])=Sheet1[Order Co DATE (Year)]
	&&
	EARLIER(Sheet1[Order Co MONTH])=Sheet1[Order Co MONTH]
	&&
	EARLIER(Sheet1[partner_prog_id])=Sheet1[partner_prog_id]
)
,Sheet1[TL APPROVALS])

    In Data page

     

    In Edit quries