Calculate MAX date when using dates selected in slider

[ConnieMaldonado]

I have a table with VP, Region, Area, Technicians with sales by date. I have a date slider and an org slicer to look at sales by date range and whatever org hierarchy is desired.

VP	Region	Area	Tech Name	Tech Login	Invoice Date	Hire Date	Sales
North	Connecticut	North Area 1	John Smith	A12345	9/1/2020	1/1/2017	$        1,200
North	Connecticut	North Area 1	John Smith	A12345	9/2/2020	1/1/2018	$           800
North	Connecticut	North Area 1	John Smith	A12345	9/3/2020	1/1/2017	$           775
North	Connecticut	North Area 2	Jose Perez	A23456	9/3/2020	8/1/20	$        2,035
North	Connecticut	North Area 2	Jose Perez	A23456	9/4/2020	1/1/2017	$        1,280
North	New York	North Area 3	Jane Smith	B12345	10/3/2020	1/1/2018	$           327
North	New York	North Area 3	Jane Smith	B12345	10/8/2020	1/1/2017	$        2,055
North	New York	North Area 4	Juan Valdez	B23456	10/15/2020	8/1/20	$        1,645
North	New York	North Area 4	Juan Valdez	B23456	10/20/2020	1/1/2017	$        1,835
North	New York	North Area 4	Cindy Jones	C12345	11/1/2020	1/1/2018	$        1,190
South	Florida	South Area 1	Ted Hall	D12345	11/2/2020	1/1/2017	$           845
South	Florida	South Area 1	Ted Hall	D12345	11/3/2020	8/1/20	$        1,320
South	Florida	South Area 1	Ted Hall	D12345	11/3/2020	1/1/2017	$        1,695
South	Florida	South Area 1	Ted Hall	D12345	11/4/2020	1/1/2018	$        1,550
South	Florida	South Area 1	Maria Pita	E12345	11/10/2020	1/1/2017	$        1,305
South	Florida	South Area 1	Maria Pita	E12345	11/15/2020	8/1/20	$        1,290
South	Florida	South Area 1	Maria Pita	E12345	11/16/2020	1/1/2017	$        1,255
South	Florida	South Area 2	John Guthrie	F12345	11/20/2020	1/1/2018	$           302
South	Florida	South Area 2	John Guthrie	F12345	11/22/2020	1/1/2017	$        2,030
South	Georgia	South Area 2	Larry Alvarez	G12345	11/28/2020	8/1/20	$        1,620
South	Georgia	South Area 2	Larry Alvarez	G12345	11/29/2020	1/1/2017	$        1,810
South	Georgia	South Area 2	Larry Alvarez	G12345	12/1/2020	1/1/2018	$        1,165
South	Georgia	South Area 2	George Lucas	H12345	12/3/2020	1/1/2017	$           820
South	Georgia	South Area 2	George Lucas	H12345	12/3/2020	8/1/20	$        1,295
South	Texas	South Area 3	Brian Dean	J12345	12/4/2020	1/1/2017	$        1,670
South	Texas	South Area 3	Brian Dean	J12345	12/5/2020	1/1/2018	$        1,525

I have created measures to calculate the average weekly sales between the date parameters selected in the slider. The calculations work nicely and show average sales by VP, Region, Area, etc. 

Here are the measures:

DENOMINATOR # Techs = CALCULATE (DISTINCTCOUNT ( SalesByTech[TechLogin] ))

NUMERATOR Sales = Sum(SalesByTech[Sales])

Since I want average weekly sales, I calculated the number of weeks between the dates selected in the slider:

WEEKS Between Slider Dates = DATEDIFF(CALCULATE(MIN('DATE Dim'[DATE]),ALLSELECTED('DATE Dim')),CALCULATE(MAX('DATE Dim'[DATE]),ALLSELECTED('DATE Dim')),WEEK)

Finally, I created a measure to calculate the average sales:

AVERAGE Sales = Divide(SalesByTech[NUMERATOR Sales],(SalesByTech[DENOMINATOR # Techs] * SalesByTech[WEEKS Between Slider Dates]))

My issue is that I need to accommodate the scenario when a tech is hired between the slider dates.  For that tech, the number of weeks should be based on the later of (hire date and first date selected in the slider) and ending slider date.

So if the slider dates are 1/1/20 and 10/1/20, and the tech was hired on 5/1/20, the number of weeks calculation must be based on 5/1/20 and 10/1/20.

I tried updating the WEEKS Between Slider Dates measure above to use max (hire date, first slider date selected) as follows:

WEEKS Between Slider Dates = DATEDIFF(MAX(SalesByTech[Hire Date],CALCULATE(MIN('DATE Dim'[DATE]),ALLSELECTED('DATE Dim'))),CALCULATE(MAX('DATE Dim'[DATE]),ALLSELECTED('DATE Dim')),WEEK)

I receive the following message:

“A single value for column 'Hire Date' in table 'SalesByTech' cannot be determined. 

I can’t figure out how to overcome it.  I’ve tried a column value (rather than a measure) to calculate the max of hire date and slider date selected, but that didn’t work.  Any help would be appreciated!  Thank you.

[ConnieMaldonado · Accepted solution]

Thank you Parry!  I couldn't get that to work.  For some reason, it doesn't like the VAR statements within the SUMX function.  Here's what I entered:

Denominator =

VAR _startDate = min( 'DATE Dim'[DATE] )

VAR _endDate = max( 'DATE Dim'[DATE] )

RETURN

sumx (

VALUES ( RevenueByTech[TechLogin] ),

VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )

VAR _minDate = MAX ( _joinDate, _startDate )

DATEDIFF ( _minDate, _endDate, WEEK )

)

I can't figure out what I'm doing wrong in the syntax.  I've tried various things and typed everything in from scratch to make sure.  Help!

Correct syntax:

Denominator =

VAR _startDate = min( 'DATE Dim'[DATE] )

VAR _endDate = max( 'DATE Dim'[DATE] )

RETURN

sumx (

VALUES ( RevenueByTech[TechLogin] ),

VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )

VAR _minDate = MAX ( _joinDate, _startDate )

RETURN DATEDIFF ( _minDate, _endDate, WEEK )

)

[parry2k]

@ConnieMaldonado would you mind accepting my reply as a solution. 😁

[parry2k]

@ConnieMaldonado Glad it worked. 🙏

Appreciate your feelings and kind words. Enjoy!!! and never hesitate to reach out. Cheers!!

LIFE IS GOOD 👍

[parry2k · Accepted solution]

@ConnieMaldonado sorry my mistake, missed return

Denominator =
VAR _startDate = min( 'DATE Dim'[DATE] )
VAR _endDate = max( 'DATE Dim'[DATE] )
RETURN
sumx (
VALUES ( RevenueByTech[TechLogin] ),
VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )
VAR _minDate = MAX ( _joinDate, _startDate )
RETURN
DATEDIFF ( _minDate, _endDate, WEEK )
)

[parry2k]

@ConnieMaldonado I guess this what you need to do with denominator

Denominator= 
VAR __startDate = MIN ( DateTable[Date] )
VAR __endDate = MAX ( DateTable[Date] )
RETURN
SUMX ( 
VALUES ( Table[Emp] ), 
VAR __joinDate = CACLULATE ( MAX ( Table[HireDate] ) )
VAR __minDate = MAX ( __joinDate, __startDate )
DATEDIFF ( __minDate, __endDate, WEEK )
)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ConnieMaldonado · Accepted solution]

Thank you Parry!  I couldn't get that to work.  For some reason, it doesn't like the VAR statements within the SUMX function.  Here's what I entered:

Denominator =

VAR _startDate = min( 'DATE Dim'[DATE] )

VAR _endDate = max( 'DATE Dim'[DATE] )

RETURN

sumx (

VALUES ( RevenueByTech[TechLogin] ),

VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )

VAR _minDate = MAX ( _joinDate, _startDate )

DATEDIFF ( _minDate, _endDate, WEEK )

)

I can't figure out what I'm doing wrong in the syntax.  I've tried various things and typed everything in from scratch to make sure.  Help!

Correct syntax:

Denominator =

VAR _startDate = min( 'DATE Dim'[DATE] )

VAR _endDate = max( 'DATE Dim'[DATE] )

RETURN

sumx (

VALUES ( RevenueByTech[TechLogin] ),

VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )

VAR _minDate = MAX ( _joinDate, _startDate )

RETURN DATEDIFF ( _minDate, _endDate, WEEK )

)

[ConnieMaldonado]

OHHHH!  You need a RETURN before the DATEDIFF.  That worked!  You're the best.  I feel like I should be paying you.  🙂  Thank you.