How to shape this data

[kault]

I need to create a chart that shows by month, how many first time registrants there are vs how many repeat registrants, relative to that point in time. I am stuck on how to manipulate the data to accomplish this.

Here's the current data I have. I'm stumped on how to manipulate this.

Contact ID	Campaign ID	Campaign Date
Joe	Webinar A	January 2020
Joe	Webinar B	June 2020
Sally	Webinar B	June 2020
Joe	Webinar C	November 2020
Sally	Webinar C	November 2020

Example: Joe registers for webinar A in January, webinar B in June, webinar C in November. In January, he was a first-time registrant. In June, he is a repeat registrant.

Based on this data table, I want a chart that shows:

• January (webinar A): 1 first-timer (Joe), 0 repeats
• June (webinar B): 1 first-timer (Sally), 1 repeat (Joe)
• November (webinar C): 0 first-timers, 2 repeats (Joe and Sally)

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat]

Here are two measure expressions to use in a table visual with the Campaign Date column.

Repeat =
VAR maxdate =
    MAX ( Events[Campaign Date] )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Events[Contact ID] ),
            NOT (
                ISBLANK (
                    CALCULATE (
                        COUNT ( Events[Campaign ID] ),
                        ALL ( Events[Campaign Date] ),
                        Events[Campaign Date] < maxdate
                    )
                )
            )
        )
    ) + 0

First Timers =
VAR maxdate =
    MAX ( Events[Campaign Date] )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Events[Contact ID] ),
            ISBLANK (
                CALCULATE (
                    COUNT ( Events[Campaign ID] ),
                    ALL ( Events[Campaign Date] ),
                    Events[Campaign Date] < maxdate
                )
            )
        )
    ) + 0

Regards,

Pat

[camargos88 · Accepted solution]

@kault .

You can use this code to create a new calculated column:

FirstDate = IF('Table'[Campaign Date] = CALCULATE(MIN('Table'[Campaign Date]), ALLEXCEPT('Table', 'Table'[Contact ID])), "First-Time", "Repeat")