Forum Discussion
Display when the difference between two dates reaches a threshold for lines having a common value
Hi everyone đź‘‹ I have a database of companies and their number of employees. I get updates about these companies that appear as a new line in the database. Here is roughly what it looks like...
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" )
)
)
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" )
)
)