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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JPGV
Frequent Visitor

New Customer Monthly Conversion Rate

Hi all,

 

I'm currently working on a project related to new customer conversion rates. I would like to calculate and visualize conversion rates for new customer quotes on a monthly basis.

 

New customers in this context are customers we've sent a (or multiple) quotes to in the current selected period but not before the current selected period -3 years. Based on @EnterpriseDNA 's amazing tutorial video's I've been able to setup a basic model which works well on a total level. However, I'm running into an issue when dividing the data on a monthly basis. Link to PBIX file.

 

When I select Year = 2019 and select a specific customer, the total number of new quotes that come up is 5 (see below). This figure is correct, as there are no transactions before this time. 

Screen 1.png

However, when I list the data on a monthly basis the following returns:

Screen 2.PNG

 

 

 

It is only showing the month of the first quote and not all of the months in which quotes have been made in the period that I've selected. As you can see the total of this table is correct. However, on a monthly basis this will lead to incorrect figures.

 

I'm sure it has something to do with the filter on the date table, but so far I haven't been able to find a way to solve this. Does anyone have a recommendation on how to tackle this?


Any advice is greatly appreciated.

 

Best regards,

 

JPGV

3 REPLIES 3
parry2k
Super User
Super User

@JPGV try changing your measure like below

 

New Customers # Quotes = 
VAR selectedYear = MAX ( 'dim DimDate'[YEAR] )
VAR CustomerTM      = VALUES( 'fact FactSalesDocumentsPPG'[SellToCustomerID] )
VAR PriorCustomers  = CALCULATETABLE( VALUES ( 'fact FactSalesDocumentsPPG'[SellToCustomerID] ), 
                        'dim DimDate'[YEAR] = selectedYear - 3,
                        'dim DimDate'[YEAR] <  selectedYear ,
                        
                        //FILTER( ALLSELECTED(  'dim DimDate' ),
    
                            //'dim DimDate'[DATE] > DATEADD(FIRSTDATE( ( 'dim DimDate'[DATE] )),-3, YEAR ) &&
                            //'dim DimDate'[DATE] < MIN( 'dim DimDate'[DATE] ) ) ,
                        ALL('dim DimProductGroup'),     
                        ALL( 'dim DimSalesperson (External)'), 
                        ALL('dim DimSalesperson (Internal)') ) 

RETURN

    CALCULATE([# Quotes],EXCEPT ( CustomerTM, PriorCustomers ) )

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

JPGV
Frequent Visitor

Hi @parry2k ,

 

Thank you for your reply! Your proposal fixes my problem to some extent. When I'm filtered by customer it is now showing exactly what I want:

 

Screen 3.PNG

However, when I remove that filter the formula does not behave as expected:

Screen 4.PNG

I know that the 433 total is correct. Any idea what could cause the difference?

 

Thanks in advance.

Hi @JPGV,

 

This may be due to different filtering methods for tables.

 

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more qui

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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