cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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 MONTH partner_prog_id Payment_No TL APPROVALS Last PMT Approvals I want the Last PMT Approvals to give these results 2015 05-MAY 1 0 1,312 1,832 1 685 1,832 1,312 2 314 1,832 685 3 192 1,832 314 4 143 1,832 192 5 108 1,832 143 05-MAY Total 2,754 1,832 06-JUN 1 0 947 1,324 1 537 1,324 947 2 242 1,324 537 3 145 1,324 242 4 106 1,324 145 5 77 1,324 106 06-JUN Total 2,054 1,324 77 2015 Total 4,808 3,156 Grand Total 4,808 3,156
3 REPLIES 3
Memorable Member

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.

// if this is a solution please mark as such. Kudos always appreciated.
New Member

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!

Microsoft Employee

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors