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" )
)
)
Hi Anonymous ,
You can try formula like below:
Result =
VAR after_ =
CALCULATE (
SUM ( 'Table'[employee] ),
FILTER (
ALL ( 'Table' ),
'Table'[company] = EARLIER ( 'Table'[company] )
&& 'Table'[update] > DATE ( 2022, 10, 01 )
)
)
VAR before_ =
CALCULATE (
SUM ( 'Table'[employee] ),
FILTER (
ALL ( 'Table' ),
'Table'[company] = EARLIER ( 'Table'[company] )
&& 'Table'[update] < DATE ( 2022, 02, 01 )
)
)
RETURN
IF ( after_ / before_ > 10, "YES", "No" )
If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.