Count unique customers in a status position given Start Date and End Date

Hi there,

I’m stuck on this challenge where I neet to countdistinct Customer ID per marketing status using a Date Slicer.

Having a fact table which has the following columns:

  • “leadID” = customer ID
  • “funnel_possy” = marketing status of the Customer.
  • “date” = date where the customer change the status.

I need to calculate two columns:

  • BalanceStart: distinctcount of Customer ID per “funnel_possy” of the minimum date of the Date Slicer.
  • BalanceEnd: distinctcount of Customer ID per “funnnel_possy” of the maximum date of the Date Slicer.

This table below is one Customer ID and all the status changed through the dates:
image

In the example below is how I want it to calculate the table. Accordingly with the slider, the column “nBalanceStart” it should distinctcount how many Customer ID it contains in each “funnel_possy” status in the ealiest date of the Slicer. And the same with the “nBalanceEnd” column but distinctcount the latest date of the slicer.

Mkt Report - Forum.pbix (153.9 KB)
Thank you.

Hi @dguedes, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

1 Like

Hi @dguedes,

You can try below and check, it is working fine with me but you might need to do thorogh testing and tweaking.

nBalanceEnd = 

VAR selectedmax =
    CALCULATE ( MAX ( 'Sample'[New_Date] ), ALL ( 'Sample'[funnel_possy] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sample'[leadID] ),
        FILTER ( ALL ( 'Sample'[New_Date] ), 'Sample'[New_Date] = selectedmax )
    )


nBalancstart = 

VAR selectedmin =
    CALCULATE ( MIN ( 'Sample'[New_Date] ), ALL ( 'Sample'[funnel_possy] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sample'[leadID] ),
        FILTER ( ALL ( 'Sample'[New_Date] ), 'Sample'[New_Date] = selectedmin )
    )

There was only 1 record for TOF on start date and on end date, so it counted as 1.

eDNA-Mkt Report - Forum.pbix (157.5 KB)

1 Like

Hi @dguedes, we’ve noticed that no response has been received from you since the 13th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi hafizsultan,

first of all, thanks for givin time to help me out here… hehe.

You logic is working when filter “LeadID” on the report. But what I was looking to achieve is nBalanceStart and nBalanceEnd to give me the count of ID in each funnel_possy accordingly with the DateSlicer (Min and Max Date).

So, for example, using the dates 01/10/2014 to 06/05/2017 in the slicer, there is 114 distinct LeadIDs.
** if you sum the column “DistCount LeadID” in this table below will see that are repetition of ID’s between the “funnel_possy” because of several events that each LeadID could have in the dataset, and that is what I want to avoid in my calculation to give a position of “funnel_possy” per Start and End date.

So, instead of show just “1” in nBalanceStart and nBalanceEnd, it should show the amount of LeadIDs that was in the selected period (Date Slicer) on each “funnel_possy”, where:

nBalanceStart = it should count how many LeadIDs was in each “funnel_possy” on the minimum date of the selection (01/10/2014). *Note, some IDs will not have events on this specific min date, so the formula should bring the closest min date based on the min selected date (ie. using the lead id “hy-1168476” for this date selection the minimum date will be “12/06/2016” on TOF.

nBalanceEnd = it should count how many LeadIDs was in each “funnel_possy” at the maximum date of the selection (06/05/2017). *Same of nBalanceStart note, not all ID’s will have events on this specific max date, so the formula should bring the closest max date based on the max selected date (ie. for this lead ID “hy104149” the using the closest maximum date it will be “06/04/2017” on MOF.

image

So, if you filter by the LeadID that I mentioned above you will see the min and max date that we need to distinct count the LeadID in the right position of the “funnel_possy”:
eDNA-Mkt Report - Forum (1).pbix (156.2 KB)

Sorry for the big explanation and thank you for your effort.

Hi - Your explanation is not clear to me. Can you please provide the expected outcome for filtered dates.

Thanks
Ankit J

Hi Ankit,

the outcome is the last table on the below post. It should have 114 leadID in each column of nBalanceEnd and nBalanceStart given the date filtered.

Thanks.

Hi @dguedes

Ok but this is the problem I can’t understand looking at the data in PBIX file and your explanation how are you getting to this Output.

Thanks

Thanks for posting your question @dguedes. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hi ankit,

sorry for that, my english is not amazing.
The sample dataset shown status (column “funnel_possy”. Which the values are designated in TOF, MOF, BOF and null) per date and per LeadID (buyers).

The output: I want to be able to create a table that shows per “funnel_possy” column how many distinct LeadID on that date has. The tricky is, not all LeadID it contains all the dates that I could possibly select on the slicer, and on that case I want to count those LeadID on the “funnel_possy” that has the latest date on the dataset.

ie.: If you look just one LeadID as an example, this lead could have the following “funnel_possy” per dates:
01/01/2019 - TOF
01/03/2019 - MOF
01/07/2019 - BOF
01/10/2010 - MOF
01/11/2010 - BOF

If you select the slicer date as 01/08/2019, the output that I’m expecting to see is:

TOF = 0
MOF = 0
BOF = 1

Which is the latest date of the date slicer contained in the dataset: 01/07/2019 - BOF

Is that make sense?

Thanks again for the effort on this.

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum