cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prices35 Frequent Visitor
Frequent Visitor

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 what you see in the "Last PMT Approvals" below.  Can someone please help me find a DAX formula that will work to give me the desired results I have placed in the last column of the table below?  TIA



Order Co DATE (Year)Order Co MONTHpartner_prog_idPayment_NoTL APPROVALSLast PMT Approvals I want the Last PMT Approvals to give these results
201505-MAY101,3121,832  
   16851,832 1,312
   23141,832 685
   31921,832 314
   41431,832 192
   51081,832 143
 05-MAY Total  2,7541,832  
 06-JUN109471,324  
   15371,324 947
   22421,324 537
   31451,324 242
   41061,324 145
   5771,324 106
 06-JUN Total  2,0541,324 77
2015 Total   4,8083,156  
Grand Total   4,8083,156  
3 REPLIES 3
samdthompson Established Member
Established Member

Re: Referencing Unrelated Data

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 Frequent Visitor
Frequent Visitor

Re: Referencing Unrelated Data

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!

Moderator Eric_Zhang
Moderator

Re: Referencing Unrelated Data

@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
捕获0.PNG

 

In Edit quries 

 

捕获.PNG捕获2.PNG捕获3.PNG捕获4.PNG

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 42 members 914 guests
Please welcome our newest community members: