Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 :

 

The date format is DD/MM/YY.

The list of companies is very long and I don’t control when I get updates.

 

What I want to achieve is : Filtering only the companies whose number of employees rose above a given threshold (for instance 10%) between two dates.

 

In a barbaric code it would look like :

DISPLAY IF ( [Employees] of the same [Company] with [Update] after 01/10/22 ) / ( [Employees] of the same [Company] with [Update] before 01/02/22 ) > 1,10

 

Can you guys help me format a DAX command to do this ? Or at least advise functions you think would be the best suited.

 

Many thanks đŸ™

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

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

            )

        )

4 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Many thanks, for your precious help v-henryk-mstf . It is greatly appreciated 🙏

     

    I shared below, a small sample of the data. I added on the sample, the expected result.

     

    I could create a new column “Result”. It runs, no syntax error detected, but the result is quite different from expected :

    • I have lines that are “YES”, however there is only one line for the company so there can’t be a comparison. For instance “2° Investing Initiative”.
    • The few expected “YES” are not identified. For instance “2Empower”.
    • On the contrary, there are companies identified “YES” while they shouldn’t. For instance “73 Strings”.

     

    Here is your script, adapted to my real database labels :

     

    Result =

    VAR after_ =

        CALCULATE (

            SUM ( 'Table'[employees_count]),

            FILTER (

                ALL ( 'Table' ),

                'Table'[company_name] = EARLIER ( 'Table'[company_name] )

                    && 'Table'[updated_at] > DATE ( 2022, 10, 01 )

            )

        )

    VAR before_ =

        CALCULATE (

            SUM ( 'Table'[employees_count]),

            FILTER (

                ALL ( 'Table' ),

                'Table'[company_name] = EARLIER ( 'Table'[company_name] )

                    && 'Table'[updated_at] < DATE ( 2022, 05, 30 )

            )

        )

    RETURN

        IF ( after_ / before_ > 10, "YES", "No" )

     

    Do you see where the error could originate ?

    I noticed that the result doesn't change when the threshold in line 21 change.

    Many thanks for your help,

     

    ------------------------------------------------------------------------------------------------------------------

     

    Sample :

    company_nameemployees_countupdated_atexpected result
    2Empower572022-08-21 07:56:16YES
    2Empower852022-09-22 15:46:42YES
    ❀Positive thought ☀ Daily Good News7502022-06-30 09:03:33YES
    ❀Positive thought ☀ Daily Good News8452022-08-23 08:35:18YES
    ❀Positive thought ☀ Daily Good News8452022-08-23 11:17:16YES
    2° Investing Initiative402022-10-04 10:59:20No
    73 Strings452022-08-21 08:02:40No
    73 Strings462022-08-28 08:01:52No
    73 Strings462022-09-22 15:54:29No
    73 Strings472022-10-04 10:45:28No
    A l'écoute d'opportunités212022-10-13 16:15:51No
    .1362022-10-04 07:39:50No
    .1352022-10-06 07:39:11No

     

    Result:

    company_nameemployees_countupdated_atResult
    2Empower572022-08-21 07:56:16No
    2Empower852022-09-22 15:46:42No
    ❀Positive thought ☀ Daily Good News7502022-06-30 09:03:33No
    ❀Positive thought ☀ Daily Good News8452022-08-23 08:35:18No
    ❀Positive thought ☀ Daily Good News8452022-08-23 11:17:16No
    2° Investing Initiative402022-10-04 10:59:20YES
    73 Strings452022-08-21 08:02:40YES
    73 Strings462022-08-28 08:01:52YES
    73 Strings462022-09-22 15:54:29YES
    73 Strings472022-10-04 10:45:28YES
    A l'écoute d'opportunités212022-10-13 16:15:51YES
    .1362022-10-04 07:39:50YES
    .1352022-10-06 07:39:11YES

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

            )

        )