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