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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gomezc73
Helper IV
Helper IV

Detect if a vendor is New or Old.

Hi,

 

  I'm stuck trying to identify within a date range if a vendor is new or not in a report of payments by vendor.

 

  I am working with 3 tables.

  Pagos : it has all payments for each vendor

  Fechas: is a calendar table (it has an active relationship with the table 'Pagos' with the field Payment date 

  Added : it has the vendor code and the date of creation (it has a 'NO active' relationship with the table 'Fechas')

 

   The idea is when the user selects a range of dates from the slicer (From the table 'Fechas'), i am showing all the payments for each vendor in that range of dates(that is working fine),

 

    But now the user requires to know  If the vendor was created during that same date range (it is a new vendor) or if the vendor was created before (it is an old vendor). the date of creation of the vendor is in the table 'Added'.

 

    I tried to do that with different ways but i can find a formula to do that.. Thank you very mch!!

    

gomezc73_1-1684230217382.png

 

Capture.JPG

 

1 ACCEPTED SOLUTION
JoBI
Resolver I
Resolver I

Hello @gomezc73 ,

 

I think for what you want this works:

First you need to create an active relatioship (both directional) between Pagos[vendor code] and Added[vendor code]. Then create this measure:

New_Old Vendor = 
Var _start = MIN(Fechas[Date])
Var _end = MAX(Fechas[Date])
Var _vendor_Added = MIN(Added[CREATION DATE])
Return 
IF(_vendor_Added>=_start && _vendor_Added<=_end, "new", "old")

The slicer is based on the Fechas[Date]. 

Hope this helps.

View solution in original post

3 REPLIES 3
JoBI
Resolver I
Resolver I

Hello @gomezc73 ,

 

I think for what you want this works:

First you need to create an active relatioship (both directional) between Pagos[vendor code] and Added[vendor code]. Then create this measure:

New_Old Vendor = 
Var _start = MIN(Fechas[Date])
Var _end = MAX(Fechas[Date])
Var _vendor_Added = MIN(Added[CREATION DATE])
Return 
IF(_vendor_Added>=_start && _vendor_Added<=_end, "new", "old")

The slicer is based on the Fechas[Date]. 

Hope this helps.

I made some changes and now works!!. thank you!

 

Thank you for respond to my issue. I tried with your solution but i am still receiving wrong values.

 

i ran the report for April+May and all vendors created in April must show 'New' and it is showing 'Old'

Capture.JPG

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.