Return Values Before a Certain Date

[Yknoff1]

Hello, I'm having some difficulties creating a visual that will show me some totals of line items from a contract. Some line items get added to a contract later on and I would like to be able to select a  previous payment and only show the items that would have existed at that time of that payment.

The result I get in the top table only shows 1 line, the line that had an amount payable in payment 3, however I would like to show all lines that were created at the time of payment 3 (lines 1-4) since the payment date was Sep 15. 

This is the measure I am using:

Contract Tender Value =

VAR Paymentdate = CALCULATE(MIN(Payment[Created On]))

RETURN

CALCULATE(

    SUM('Line Items'[Amount]),

'Line Items'[Change Order] = FALSE() &&

'Line Items'[Created On] <= Paymentdate

)

These are the realtionships in the table:

Thanks for any help. 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Yknoff1]

Hi Ashish, Here is a link to the pbix file Contract Data.pbix

[Ashish_Mathur]

And what result do you want.  Explain and show that clearly.  Your first post is not at all clear.

[Yknoff1]

The result I want is when I select payment number 3 the table will display line #1, #2, and #3. Payment 3 was created on September 15 and lines #1-3 were created previous to that date however lines #4 and #5 were created after so I do not want to see them if I have payment 3 selected. 

Currently if I select payment number 3 it will display only line #2.  

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Yknoff1]

Great thanks for the help!

[Ashish_Mathur]

You are welcome.