cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

kind of lookup

I have to create this report anyone has any suggestion for me this is the senario :

 

I have two table this my tables

 

table A

Posting date

Quantity

Amount

2019-11-05

25

19802

2019-11-06

75

25600

2019-11-25

80

102500

 

Table B

 

Actual date

End of period date

2019-11-05

2019-11-23

2019-11-06

2019-11-23

2019-11-25

2018-12-25

 

if the posting date in table A  is greater thanEnd of  period  date then  return Amount else 0 

ghow can I do this in power BI ?

 

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: kind of lookup

@Mniknejad to understand the tables bette, for each posting date there will be unique actual date record in table b?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: kind of lookup

@parry2k  yes it is 

Highlighted
Super User IV
Super User IV

Re: kind of lookup

@Mniknejad easiest would be create relationship between Actual Date and Posting Date, actual date table will be on one side of the relationship and then add new column in your posting date table

 

New Amount = IF ( Data[Posting date] > RELATED( 'Data Actual'[End of period date] ), Data[Amount], 0 )

 

and here is the output

 

image.png






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors