Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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.
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.
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
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |