Forum Discussion
DAX - count between 2 dates
Hi phil91 ,
In terms of the active relationship, I guess this would be personal preference to some degree. If your visuals most-frequently utilise metrics based on [start date], then make this one active and vice-versa. If there's no difference, then I tend to make them all inactive to avoid confusion later on.
Regarding number employed during the period, you'll need a value-over-time measure, something like this:
_noofEmployed = VAR date_to_examine = MAX(calendar[date]) VAR noofEmployed = CALCULATE( CALCULATE( DISTINCTCOUNT( yourTable[employeeCode]), KEEPFILTERS( date_to_examine >= yourTable[start date]), KEEPFILTERS( date_to_examine <= yourTable[leave date]) ), CROSSFILTER(calendar[date], yourTable[relatedDateFieldIfUsed], None) ) RETURN IF (ISBLANK(noofEmployed ), BLANK(), noofEmployed )You'll notice that I've removed the crossfilter in this example as this works only when unrelated. If you make both of your relationships inactive, then you can remove the first CALCULATE and the CROSSFILTER line.
Pete
Ok, let's focus on [Pete to the Rescue - v2] as this gives you what you want, and is the fastest out of all that do give you that (I'm surprised that RELATED is faster than separated dimension TBH, but there we are).
Let's break it up to see if we can find the culprit:
PttRv2_1 =
VAR __currDate =
MIN('DIM - Date'[Full Date])
RETURN
CALCULATE(
DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __currDate >= 'FACT - Customer Status'[Start Date]
&& __currDate < 'FACT - Customer Status'[End Date]
)
)
PttRv2_2 =
VAR __currDate =
MIN('DIM - Date'[Full Date])
VAR __prevDate =
DATE(
YEAR(__currDate) -3,
MONTH(__currDate),
DAY(__currDate)
)
RETURN
CALCULATE(
DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __prevDate >= 'FACT - Customer Status'[Start Date]
&& __prevDate < 'FACT - Customer Status'[End Date]
)
)
Pete
Ok, fair warning, this is going to be a large measure dump. I went down this path before, isolating each side of the division and seeing if I can't get one of them to run quickly. What you have as PttRv2_1 is what I am calling [Active Customer Count - X Period End] while PttRv2_2 is [Active Customer Count - X Period Start]. Not great names but they work for now. I noticed before that [Active Customer Count - X Period Start] was slower so I started by trying to optimize this one.
First, your two measures, plus one tweak I added (countrows instead of distinctcount())
PttRv2_1 =
VAR __currDate =
MIN('DIM - Date'[Full Date])
RETURN
CALCULATE(
DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __currDate >= 'FACT - Customer Status'[Start Date]
&& __currDate < 'FACT - Customer Status'[End Date]
)
)
PttRv2_1_Countrows =
VAR __currDate =
MIN('DIM - Date'[Full Date])
RETURN
CALCULATE(
COUNTROWS('FACT - Customer Status'),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __currDate >= 'FACT - Customer Status'[Start Date]
&& __currDate < 'FACT - Customer Status'[End Date]
)
)
PttRv2_2 =
VAR __currDate =
MIN('DIM - Date'[Full Date])
VAR __prevDate =
DATE(
YEAR(__currDate) -3,
MONTH(__currDate),
DAY(__currDate)
)
RETURN
CALCULATE(
DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __prevDate >= 'FACT - Customer Status'[Start Date]
&& __prevDate < 'FACT - Customer Status'[End Date]
)
)
PttRv2_2_Countrows =
VAR __currDate =
MIN('DIM - Date'[Full Date])
VAR __prevDate =
DATE(
YEAR(__currDate) -3,
MONTH(__currDate),
DAY(__currDate)
)
RETURN
CALCULATE(
DISTINCTCOUNT('FACT - Customer Status'[Account Number]),
FILTER(
'FACT - Customer Status',
'FACT - Customer Status'[Status] IN {"New Customer", "Reanimated Customer", "Active Customer - New", "Active Customer - Reanimated"}
&& __prevDate >= 'FACT - Customer Status'[Start Date]
&& __prevDate < 'FACT - Customer Status'[End Date]
)
)
Results:
And here are all the versions I tried of [Active Customer Count - X Period Start]. They range in performance from "oh, it's still running" to "something's wrong":
Active Customers - X Period Start - v2 =
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
CALCULATETABLE (
SUMMARIZE (
'FACT - Customer Status',
'FACT - Customer Status'[Account Number],
"Status",
CALCULATE (
VALUES ( 'DIM - Status'[Status] ),
TOPN (
1,
SUMMARIZE (
'FACT - Customer Status',
'DIM - Status'[Status],
'DIM - Date'[Full Date]
),
'DIM - Date'[Full Date], DESC
)
)
),
DATESBETWEEN (
'DIM - Date'[Full Date],
BLANK (),
MIN ( 'DIM - Date'[Full Date] )
),
USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] )
),
[Status] = "Active"
)
),
'DIM - Date'[Full Date] = _PeriodStartDate
)
Active Customers - X Period Start - v3 =
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
COUNTROWS(
FILTER (
GENERATE (
SUMMARIZE (
'FACT - Customer Status',
'DIM - Status'[Status],
'FACT - Customer Status'[Start Date],
'FACT - Customer Status'[End Date]
),
DATESBETWEEN (
'DIM - Date'[Full Date],
'FACT - Customer Status'[Start Date],
'FACT - Customer Status'[End Date]
)
),
[Full Date] = _PeriodStartDate
&& [Status] = "Active"
)
)
Active Customers - X Period Start - v4 =
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
CALCULATETABLE (
SUMMARIZE (
'FACT - Customer Status',
'FACT - Customer Status'[Account Number],
"Status",
CALCULATE (
VALUES ( 'DIM - Status'[Status] ),
TOPN (
1,
SUMMARIZE (
'FACT - Customer Status',
'DIM - Status'[Status],
'FACT - Customer Status'[Start Date]
),
'FACT - Customer Status'[Start Date], DESC
)
)
),
DATESBETWEEN (
'DIM - Date'[Full Date],
BLANK (),
MIN ( 'DIM - Date'[Full Date] )
),
USERELATIONSHIP ( 'DIM - Date'[Date_Key], 'FACT - Customer Status'[StatusStartDate_Key] )
),
[Status] = "Active"
)
),
'DIM - Date'[Full Date] = _PeriodStartDate
)
Active Customers - X Period Start - v5 =
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate =
DATE ( YEAR ( [Selected Date] ) - _PeriodYears, MONTH ( [Selected Date] ), DAY ( [Selected Date] ) )
RETURN
CALCULATE (
COUNTROWS (
FILTER (
CALCULATETABLE (
SUMMARIZE (
'FACT - Customer Status',
'FACT - Customer Status'[Account Number],
"Status",
CALCULATE (
VALUES ( 'DIM - Status'[Status] ),
TOPN (
1,
SUMMARIZE (
'FACT - Customer Status',
'DIM - Status'[Status],
'FACT - Customer Status'[Start Date]
),
'FACT - Customer Status'[Start Date], DESC
)
)
),
'FACT - Customer Status'[Start Date] < _PeriodStartDate
),
[Status] = "Active"
)
)
)
Active Customers - X Period Start - v6 =
VAR _PeriodYears = [Period Length Value]
VAR _PeriodStartDate = DATE(YEAR([Selected Date]) - _PeriodYears, MONTH([Selected Date]), DAY([Selected Date]))
RETURN
CALCULATE (
COUNTROWS ( 'FACT - Customer Status' ),
'FACT - Customer Status'[Start Date] < _PeriodStartDate,
'FACT - Customer Status'[End Date] > _PeriodStartDate,
'DIM - Status'[Status] = "Active"
)
I can update this message with the results of the second set of measures later, but I have a meeting now. Thanks again for all the help.