Rolling Back Time Periods

Question

Please see the following screenshot and measure.  (i had a previous post about creating a distribution that i was helped on).  I have a slicer with #'s ie (1,2,3,4,5,6,12,etc). This time slicer is used to rollback date periods.  I only want the user to rollback from the lastdate in my sales table. i use the formula below.  My time table (Time) has no relationship to any other tables.  [Distro] is definted Distinctcount(Sales[Store name]).

At first, the measure seems to work however, i realize that the Rep count doesnt add up to the 17 (see below)

CALCULATE([Distro], DATESBETWEEN( dimdate[datekey], DATEADD(LASTDATE(VALUES(sales[date])),

MAX(Time[X])*-1, MONTH  ) , DATEADD(

FIRSTDATE(VALUES(dimdate[datekey])), max(Time[X])*-1, MONTH ) ) )

Answer 1

Your measure is based on a DISTINCTCOUNT. As a consequence it is not additive.

Simply put, if Store 1 appears under Cindy and Ross Jones, it will be counted only once for each rep but also only once only for both reps considered together.

If you want the measure for any group of representatives to be the sum of the measure for each representative in that group, then you will need to use a SUMX function.

So, this could like this:

CALCULATE( 
 SUMX( 'Reps',
  [Distro]
 ),
 ...)

Alternatively, you could change the measure itself. (Probably better) 

[Distro] := SUMX('Reps', 
		CALCULATE( DISTINCTCOUNT( Sales[Store name]) )
)

Note, that the measure will still not add up whenever you any other dimension in your report. Make sure you define the granularity level from which you want to aggregate in your SUMX expression.

---

The Data Specialist (Blog)