Forum Discussion

robarivas's avatar
robarivas
Post Patron
8 years ago

Measure using EARLIER??

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 :-)

Need Help

8 Replies

  • robarivas's avatar
    robarivas
    Post Patron
    8 years ago

    Really stumped on this.

     

    :smileysad:

     

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

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    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's avatar
      robarivas
      Post Patron
      8 years ago

      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 ASum of Transactions
      1-Jan$20
      3-Jan$65
      9-Jan$175

       

    • robarivas's avatar
      robarivas
      Post Patron
      8 years ago

      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)