Calculate Number of Days Between Slicer Dates

[ConnieMaldonado]

Hello:

I need to calculate average revenue by week by technician for dates entered via the standard date slicer.  So if the user selects 9/3/20 and 12/15/20 in the date slicer, I want to display the average revenue per week by technician.  I also have a slicer based on org (VP and Region) and need the rollups to calculate correctly.

I need to sum the revenue between the slicer dates, as well as calculate the number of weeks and distinct techs between the slicer dates.  Once I have that, I can create a measure, do the division and calculate the average. 

My table has VP, Region, Tech Name, Tech Login, Invoice Date, Revenue Amt.  I also have a date dimension.

VP	Region	Tech Name	Tech Login	Invoice Date	Revenue Amt
North	Connecticut	John Smith	A12345	9/1/2020	$        1,200
North	Connecticut	John Smith	A12345	9/2/2020	$           800
North	Connecticut	John Smith	A12345	9/3/2020	$           775
North	Connecticut	Jose Perez	A23456	9/3/2020	$        2,035
North	Connecticut	Jose Perez	A23456	9/4/2020	$        1,280
North	New York	Jane Smith	B12345	10/3/2020	$           327
North	New York	Jane Smith	B12345	10/8/2020	$        2,055
North	New York	Juan Valdez	B23456	10/15/2020	$        1,645
North	New York	Juan Valdez	B23456	10/20/2020	$        1,835
North	New York	Cindy Jones	C12345	11/1/2020	$        1,190
South	Florida	Ted Hall	D12345	11/2/2020	$           845
South	Florida	Ted Hall	D12345	11/3/2020	$        1,320
South	Florida	Ted Hall	D12345	11/3/2020	$        1,695
South	Florida	Ted Hall	D12345	11/4/2020	$        1,550
South	Florida	Maria Pita	E12345	11/10/2020	$        1,305
South	Florida	Maria Pita	E12345	11/15/2020	$        1,290
South	Florida	Maria Pita	E12345	11/16/2020	$        1,255
South	Florida	John Guthrie	F12345	11/20/2020	$           302
South	Florida	John Guthrie	F12345	11/22/2020	$        2,030
South	Georgia	Larry Alvarez	G12345	11/28/2020	$        1,620
South	Georgia	Larry Alvarez	G12345	11/29/2020	$        1,810
South	Georgia	Larry Alvarez	G12345	12/1/2020	$        1,165
South	Georgia	George Lucas	H12345	12/3/2020	$           820
South	Georgia	George Lucas	H12345	12/3/2020	$        1,295
South	Texas	Brian Dean	J12345	12/4/2020	$        1,670
South	Texas	Brian Dean	J12345	12/5/2020	$        1,525
South	Texas	Brian Dean	J12345	12/7/2020	$        1,280
West	California	Charlie Jones	K12345	12/7/2020	$        1,265
West	California	Charlie Jones	K12345	12/8/2020	$        1,215
West	California	Sara Luz	L12345	12/10/2020	$        1,265
West	Utah	Ted Lands	M12345	12/15/2020	$        1,315
West	Utah	Ted Lands	M12345	12/16/2020	$        1,365
West	Utah	Harry Pitcher	N12345	12/18/2020	$        1,415

Summing the revenue is easy as it will sum based on the slicer dates.  I created a measure to calculate number of distinct techs based on login. My issue is with calculating the number of weeks between the dates selected in the slicer.  How do I reference the slicer dates entered by the user so I can calculate the number of weeks between the 2 dates? 

Any help would be appreciated.  Thanks!

[Syndicate_Admin]

Well, can someone help me count the number of days from a date slicer?

[Ashish_Mathur]

Hi,

Assuming the date slicer has been built from the Calendar Table, write this measure

Diff = max(calendar[date])-min(calendar[date])

Hope this helps.

[amitchandak]

@ConnieMaldonado , You can create a date table and week there and then you can divide by distinct weeks

new columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

or divide by

datediff(minx(allselected('Date'), 'Date'[Date]),maxx(allselected('Date'), 'Date'[Date]), WEEK)

datediff(minx(allselected('Date'), 'Date'[Date]),maxx(allselected('Date'), 'Date'[Date]), Day)/7

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ConnieMaldonado · Accepted solution]

Thanks so much for your solutions!  I was able to calculate the number of weeks from the slicer dates using the following measures:

/* First date in slicer:

Selected Value MIN = CALCULATE(MIN('DATE Dim'[DATE]),ALLSELECTED('DATE Dim'))

/* Second date in slicer:

Selected Value MAX = CALCULATE(MAX('DATE Dim'[DATE]),ALLSELECTED('DATE Dim'))

/* Number of weeks between the 2 dates:

DAYS Between Slicer Dates = DATEDIFF(Revenue[Selected Value Min],Revenue[Selected Value MAX],WEEK)

I could have put this all in one formula but broke it up for illustrative purposes.

Couldn't have done it without you all!  Thanks.

[Greg_Deckler]

@ConnieMaldonado - Well, you can get the dates from the slicer using MIN and MAX. You could then use CALENDAR to create a date table as a VAR between those dates. Use ADDCOLUMNS to add a column using WEEKNUM. Then use DISTINCT and COUNTROWS to get the distinct weeks. Something along those lines.

[dannyb1103]

"DAX is easy, CALCULATE makes DAX hard..."
Appreciate you mentioning this.  I was doing this intuitively (because it was easier) and was concerned the shoe was goinig to drop one day.  TY ~ 👍