PowerPivot Distinct New User by Date

[vsridha]

Hi All, this is my first post.

I am very new to PowerPivot and I want to recreate a calculation that I have in my normal pivot table in power pivot.

Lets say I have 2 columns; Column 1: UserName, Column 2: Date. I want a PivotTable/Chart with Distinct New users by date (who have visited my website). In the normal pivot table, I had a helper column in my source table with this formula (=IF(COUNTIF($A$2:$A2, $A2)>1,0,1). So this marks the first visit of each user as 1 and my pivot sums this to give new users who visit my site each day.

How can I recreate this in POWERPIVOT (Excel 2013) without using a helper column? Can I use Calculated Measure or Calculated Column for this?

Thanks in advance for the help!

 

[vsridha]

Solved this with;

[COLOR=#333333]DistinctNew :=[/COLOR]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">    CALCULATE (
        DISTINCTCOUNT ( Table1[username] ),
        FILTER (
            ALL ( Table1[LoginDate] ),
            Table1[LoginDate] <= MAX ( Table1[LoginDate] )
        )
    )
    - CALCULATE (
        DISTINCTCOUNT ( Table1[username] ),
        FILTER (
            ALL ( Table1[LoginDate] ),
            Table1[LoginDate] < MAX ( Table1[LoginDate] )
        ) </code>[COLOR=#333333]    )[/COLOR]

 

[RoryA]

I'm glad you have a solution but for the future please read the forum rules on cross-posting and try to follow them. Thanks.