Quarterly Report- Select First Entry

[slhangen]

I need to create a quarterly report that scans a SP list and counts an contact as new if it is the first entry in a designated time period.

For Example: If I create a slicer with a begin and end date, I want to only count those entries that have had their first entry within that time period.

For example, If Geddy Rush has mulitple entries, I only want to count his first within the alotted time period.

My organization has to document how many new contacts for each quarter.  Thx so much!

Sample Data:

TitleLogStudentNameLogContactDateLogContactType

300959	ADAMS, JOHN	4/11/2024	Phone
300959	ADAMS, JOHN	2/21/2024	Phone
300959	ADAMS, JOHN	2/20/2024	Phone
280436	ALEXANDER, BILLY BOB	4/15/2024	Student Conference
290215	ALLEN, TIM	2/21/2024	Email
290215	ALLEN, TIM	2/7/2024	Student Conference
290197	ALLEN, TOM	4/22/2024	Student Conference
290197	ALLEN, TOM	3/11/2024	Student Conference
290197	ALLEN, TOM	3/11/2024	Student Conference
290197	ALLEN, TOM	3/7/2024	Student Conference
290197	ALLEN, TOM	2/8/2024	Student Conference
290197	ALLEN, TOM	1/19/2024	Student Conference
301086	ANDERSON, LEXUS CAR	1/19/2024	Student Conference

[Ritaf1983 · Accepted solution]

Hi @slhangen 
You can add a calculated column, which will rank the entries by date.

Count_times = RANKX(

    FILTER('Table', 'Table'[Id]= EARLIER('Table'[Id])),

    'Table'[date],

    ,

    ASC,

    DENSE

)

and then use a simple dax for measure with filtering first entry only

First entries = CALCULATE(DISTINCTCOUNT('Table'[Id]),'Table'[Count_times]=1)

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[slhangen]

This was amazing! Works perfectly.  Can I ask you to briefly walk me through what it does (1-2 sentences) as I am trying to expand my knowledge. Especially the DENSE function, never seen that one.

Thx again!!!

[Ritaf1983]

Hi @slhangen 

The RANKX function operates by assigning ranks to rows within a group based on a specified expression. In this instance, the expression utilized is the date column, ensuring that rows within each Id group are ranked according to their corresponding dates.

To effectively group rows based on their Id values, the FILTER function is employed. This function creates a temporary table that exclusively contains the rows corresponding to the current Id value. By leveraging the EARLIER function, the Id value from the preceding row is retrieved, ensuring that the ranking process is confined within each Id group.

The ASC parameter plays a crucial role in determining the ranking order. It specifies that the ranking should be performed in ascending order, implying that earlier dates will receive lower ranks.

To address situations where duplicate values exist, the DENSE parameter is utilized. It ensures that ranks are assigned sequentially, without any gaps, even when duplicate values are encountered. For instance, if three rows share the same date, they will be assigned ranks of 1, 2, and 3, rather than 1, 1, and 4.

More information about Rankx is here :
https://learn.microsoft.com/en-us/dax/rankx-function-dax

Oher way to give a kind of index to every row inside the id is to work with PQ , take a look at the linked tutorial :

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[slhangen]

Thank you so much, that explanation was extremely helpful.

[Ritaf1983]

Happy to help 🙂