Measure using EARLIER??

[robarivas]

Not sure if using the EARLIER function is the way to go. Even if it is, I still can't figure out how to use it properly. I am trying to sum up the transactions in one table whose transaction date is less than or equal to the date in a separate but related table (by the way these tables are connected via a bridge table using bidirectional filtering because the relationship between the 2 tables is many to many).

Conceptually my measure would be something like this:

Measure = CALCULATE( [PaymentsMeasure], TransactionsTable[Post_Date] <= TableA[Particular_Date_Field] )

The actual summation is handled in the [PaymentsMeasure] measure.

Also, I need this measure to perform very quickly of course. And I need to create multiple versions of this so I don't want to use calculated columns.

Thanks in advance for your help 🙂

[OwenAuger]

Hi @robarivas

Could you explain a bit more how you want the filtering to work in this measure, with an example?

Since there is a many-to-many relationship between TransactionsTable and TableA (hopefully I've got that right), you could have a multiple values of TransactionsTable[Post Date] 'related to' multiple values of TableA[Particular_Date_Field]. 

Say for example the rows with Transactions[Post Date] = 1 Jan...10 Jan are related to rows with TableA[Particular_Date_Field] = 5 Jan..15 Jan

Which rows would be included for the purposes of the measure?

[robarivas]

Hello again @OwenAuger

I've created a tiny .pbix example that should help demonstrate what I was looking to do. In it I created a measure. I'm trying to fix/improve that measure to match the desired results as shown in the image I inserted into the .pbix.

Is there a way for me to share that .pbix with you? (preferably email)

[OwenAuger]

@robarivas

Sure, I'll PM you the email address to send the pbix 🙂

[robarivas]

Hello @OwenAuger. Thanks for the reply. I ended up restructuring my data model to circumvent this need. But I am still curious. If Table A had rows with dates 1/1, 1/3, and 1/9 that are related to rows on the Transaction table with dates of 1/1 : $20, 1/2 : $30, 1/3 : $15, 1/4 : $25, 1/7 : $50, 1/8 : $35, and 1/11 : $80

then I want to be able to create the following view:

Table A	Sum of Transactions
1-Jan	$20
3-Jan	$65
9-Jan	$175

[Ashish_Mathur]

Hi @robarivas,

On the Transactions Table, can the Date column have duplicate entries?

[robarivas]

Hello @Ashish_Mathur. No the Transactions table won't have true duplicates. Some rows may seem like duplicates in many cases because the field values are identical but that's just because the transactions table is not grouped at all. So even when one row looks the same as another row it actually really is a totally diferent transaction.

[Ashish_Mathur]

Hi,

Assuming there will never be any duplicates date entries in Table1, try this

1. Build a relatioship from the Date column in Table2 to the Date column in Table1
2. In the visual, drag the Date from the Date column from Table1
3. Write the following measure

=SUM(Table2[Amount])

Hope this helps.

[robarivas]

Really stumped on this.

Maybe someone can at least refer me to an alternate resource where this kind of question might be quickly addressable? Thank you.