Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

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

 

 

 

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

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous 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 ) )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

       

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

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

       

      Thanks in advance.

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi Anonymous,

         

        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