Calculating Difference of counts (on columns) by date

[tjtommtj]

Hi, I have some data that is visits to a website which looks like this: 

Date	VisitID
08/01/2016	111366
09/01/2016	373823
10/01/2016	608594
11/01/2016	111366
10/01/2016	887988
13/01/2016	255210
14/01/2016	693369
10/01/2016	928357
14/01/2016	299080
09/01/2016	693369

So the dates arn't summed and the uniqueID can be on multiple dates. What I need is to be able to calculate the difference in counts of the amount of people (by VisitID). In Excel its easy to get a pivot of the dates and the count of visit id (but not distinct count). I then need it to work out the day on day difference in visits. 

Essentially I need to recreate this: 

Date	DistinctCountOfVisitID	DaybydayDifference	%Diff
08/01/2016	1
09/01/2016	2	1	200%
10/01/2016	3	1	150%
11/01/2016	1	-2	33%
13/01/2016	1	0	100%
14/01/2016	2	1	200%

Any help would be appreciated.

[Sean · Accepted solution]

@tjtommtjthese 2 Measures should do it...

MEASURE 1

DayByDayDifference = 
VAR CurrentDate = MIN ( 'Table'[Date ] )
VAR PreviousDate = CALCULATE ( MAX ( 'Table'[Date ] ), 'Table'[Date ] < CurrentDate )
RETURN
    DISTINCTCOUNT ( 'Table'[VisitID] )
        - CALCULATE ( DISTINCTCOUNT ( 'Table'[VisitID] ), 'Table'[Date ] = PreviousDate )

MEASURE 2

% Difference = 
VAR CurrentDate = MIN ( 'Table'[Date ] )
VAR PreviousDate = CALCULATE ( MAX ( 'Table'[Date ] ), 'Table'[Date ] < CurrentDate )
RETURN
    DIVIDE (
        DISTINCTCOUNT ( 'Table'[VisitID] ),
        CALCULATE ( DISTINCTCOUNT ( 'Table'[VisitID] ), 'Table'[Date ] = PreviousDate )
    )

And the results...

Hope this helps!

Good Luck!