cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ConnieMaldonado
Resolver II
Resolver II

Calculate MAX date when using dates selected in slider

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. 

ERROR Message.PNG

 

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.

2 ACCEPTED SOLUTIONS

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 )
)
 
Error2.PNG
 
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 )
)

View solution in original post

parry2k
Super User III
Super User III

@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 )
)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

6 REPLIES 6
parry2k
Super User III
Super User III

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@ConnieMaldonado Glad it worked. 🙏

 

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

 

LIFE IS GOOD 👍






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@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 )
)

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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 )
)
 
Error2.PNG
 
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 )
)

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors