Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Yknoff1
New Member

Return Values Before a Certain Date

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. 

 

Yknoff1_0-1728100245575.png

 

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:

Yknoff1_1-1728100394764.png

 

 

Thanks for any help. 

1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1728113436216.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.  

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1728113436216.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Great thanks for the help!

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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