Forum Discussion
Display when the difference between two dates reaches a threshold for lines having a common value
After some tests, I decided to stick with the command below to detect the changes. It returns "sans donnée" to signal the companies for which there is no data after or before the dates of comparison.
Many thanks again to v-henryk-mstf ; you really did 99% of the heavy lifting and it helped a lot !
I wish a great day to everybody,
Augmentation_employee_count_entre_2_dates =
VAR seuil = 5 //augmentation en pourcentage, 20 signifie +20%
VAR date_after =
DATE ( 2022, 10, 01 ) //date à partir de laquelle on regarde le changement
VAR date_before =
DATE ( 2022, 09, 01 ) //date de référence
VAR after_ =
CALCULATE (
MAX ( 'Table'[employees_count] ),
FILTER (
ALL ( 'Table' ),
'Table'[company_name] = EARLIER ( 'Table'[company_name] )
&& 'Table'[updated_at] > date_after
)
)
VAR before_ =
CALCULATE (
MAX ( 'Table'[employees_count] ),
FILTER (
ALL ( 'Table' ),
'Table'[company_name] = EARLIER ( 'Table'[company_name] )
&& 'Table'[updated_at] < date_before
)
)
RETURN
IF (
after_ = 0,
"sans donnée",
IF (
before_ = 0,
"sans donnée",
IF ( after_ / before_ > ( 1 + ( seuil / 100 ) ), "oui", "non" )
)
)
To complete my answer v-henryk-mstf , I brainstomed :
By using MAX and MIN instead fo SUM functions, like in the script below, I can compare the lowest and highest point easily.
However I lose the time dimension. The companies loosing employees are identified like the companies gaining employees. Do you see how I could filter them out ?
Result =
VAR after_ =
CALCULATE (
MAX ( 'Table'[employees_count]),
FILTER (
ALL ( 'Table' ),
'Table'[company_name] = EARLIER ( 'Table'[company_name] )
)
)
VAR before_ =
CALCULATE (
MIN ( 'Table'[employees_count]),
FILTER (
ALL ( 'Table' ),
'Table'[company_name] = EARLIER ( 'Table'[company_name] )
)
)
RETURN
IF (after_/before_ > 1.10 , "YES" , "No")