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. VPRegionAreaTech NameTech LoginInvoice Date Hire DateSalesNorthConnecticutNorth Area 1John SmithA123459/1/20201/1/2017 $ 1,200NorthConnecticutNorth Area 1John SmithA123459/2/20201/1/2018 $ 800NorthConnecticutNorth Area 1John SmithA123459/3/20201/1/2017 $ 775NorthConnecticutNorth Area 2Jose PerezA234569/3/20208/1/20 $ 2,035NorthConnecticutNorth Area 2Jose PerezA234569/4/20201/1/2017 $ 1,280NorthNew YorkNorth Area 3Jane SmithB1234510/3/20201/1/2018 $ 327NorthNew YorkNorth Area 3Jane SmithB1234510/8/20201/1/2017 $ 2,055NorthNew YorkNorth Area 4Juan ValdezB2345610/15/20208/1/20 $ 1,645NorthNew YorkNorth Area 4Juan ValdezB2345610/20/20201/1/2017 $ 1,835NorthNew YorkNorth Area 4Cindy JonesC1234511/1/20201/1/2018 $ 1,190SouthFloridaSouth Area 1Ted HallD1234511/2/20201/1/2017 $ 845SouthFloridaSouth Area 1Ted HallD1234511/3/20208/1/20 $ 1,320SouthFloridaSouth Area 1Ted HallD1234511/3/20201/1/2017 $ 1,695SouthFloridaSouth Area 1Ted HallD1234511/4/20201/1/2018 $ 1,550SouthFloridaSouth Area 1Maria PitaE1234511/10/20201/1/2017 $ 1,305SouthFloridaSouth Area 1Maria PitaE1234511/15/20208/1/20 $ 1,290SouthFloridaSouth Area 1Maria PitaE1234511/16/20201/1/2017 $ 1,255SouthFloridaSouth Area 2John GuthrieF1234511/20/20201/1/2018 $ 302SouthFloridaSouth Area 2John GuthrieF1234511/22/20201/1/2017 $ 2,030SouthGeorgiaSouth Area 2Larry AlvarezG1234511/28/20208/1/20 $ 1,620SouthGeorgiaSouth Area 2Larry AlvarezG1234511/29/20201/1/2017 $ 1,810SouthGeorgiaSouth Area 2Larry AlvarezG1234512/1/20201/1/2018 $ 1,165SouthGeorgiaSouth Area 2George LucasH1234512/3/20201/1/2017 $ 820SouthGeorgiaSouth Area 2George LucasH1234512/3/20208/1/20 $ 1,295SouthTexasSouth Area 3Brian DeanJ1234512/4/20201/1/2017 $ 1,670SouthTexasSouth Area 3Brian DeanJ1234512/5/20201/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.

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] )RETURNsumx (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] )RETURNsumx (VALUES ( RevenueByTech[TechLogin] ),VAR _joinDate = CALCULATE ( MAX ( RevenueByTech[Hire Date] ) )VAR _minDate = MAX ( _joinDate, _startDate )RETURN DATEDIFF ( _minDate, _endDate, WEEK ))

ConnieMaldonado

Responsive Resident

5 years ago

Solved

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.

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.

Need Help

ConnieMaldonado
5 years ago
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

5 years ago

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

6 Replies

parry2k
Super User
5 years ago
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
  Responsive Resident
  5 years ago
  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
    Responsive Resident
    5 years ago
    OHHHH! You need a RETURN before the DATEDIFF. That worked! You're the best. I feel like I should be paying you. 🙂 Thank you.

parry2k

Super User

5 years ago

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
Super User
5 years ago
ConnieMaldonado Glad it worked. 🙏

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

LIFE IS GOOD 👍
parry2k
Super User
5 years ago
ConnieMaldonado would you mind accepting my reply as a solution. 😁

Forum Discussion

Calculate MAX date when using dates selected in slider

6 Replies

Related content

Slider problems

Quarter calculation upto selected month

Line Chart with Slider selection

Timeline slider

Percentage Slicer (slider)