Forum Discussion

Chris2016's avatar
Chris2016
Resolver I
2 years ago
Solved

Determine user progress in historical dataset

Hello,

I have a dataset that refreshes every month and, with each refresh, a new monthly capture is added, e.g.:
https://fromsmash.com/monthly-captures 

 

I need to show Sales Persons' # of Items sold progress for those who have taken the company training (ClassPresence = "Yes") vs. those who have not (ClassPresence = "No") and divide those with "Yes" into 3 categories:
- Increased sales after classes

- Closed the first sale(s) after classes

- No change after classes
"After classes" means that the Latest Sales date is after the "ClassDate".

 

Any idea how this can be achieved in a relevant visual?

Test pbix here: https://fromsmash.com/Historic-data-insights2 


Thanks!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lbendlin ,Thanks for your quick reply, I will add more.

    Hi Chris2016 ,

    I used the following dax expression to create a calculated column, please refer to the attachment to see if it meets your needs.

    Column1 =
VAR _SalesPerson = [Sales Person]
VAR _SalesCategory = [Sales Category]
VAR _ClassDate =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
        ),
        [Column 2]
    )
VAR _maxDateBeforeClassDate =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] < _ClassDate
        ),
        [Latest Sales Date]
    )
VAR _ItemSoldBeforeClass =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] = _maxDateBeforeClassDate
        ),
        [# of Items sold]
    )
VAR _minMonthlyCaptureAfterClassDate =
    MINX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] > _ClassDate
        ),
        [Monthly capture]
    )
VAR _minMonthlyCaptureWhichBiggerBeforeClass =
    MINX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [# of Items sold] > _ItemSoldBeforeClass
        ),
        [Monthly capture]
    )
VAR _result =
    SWITCH (
        TRUE (),
        [ClassPresence] = "NO", "Not attending classes",
        [Latest Sales Date] < DATEVALUE ( [ClassDate] ), "Not attending classes",
        ISBLANK ( _maxDateBeforeClassDate )
            && [Monthly capture] = _minMonthlyCaptureAfterClassDate, "Closed the first sale(s) after classes",
        ISBLANK ( _maxDateBeforeClassDate )
            && [Monthly capture] <> _minMonthlyCaptureAfterClassDate, "Increased sales after classes",
        IF (
            ISBLANK ( _minMonthlyCaptureWhichBiggerBeforeClass ),
            "No change after classes",
            SWITCH (
                TRUE (),
                [Monthly capture] < _minMonthlyCaptureWhichBiggerBeforeClass, "No change after classes",
                [Monthly capture] = _minMonthlyCaptureWhichBiggerBeforeClass, "Closed the first sale(s) after classes",
                "Increased sales after classes"
            )
        )
    )
RETURN
    _result

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lbendlin ,Thanks for your quick reply, I will add more.

    Hi Chris2016 ,

    I used the following dax expression to create a calculated column, please refer to the attachment to see if it meets your needs.

    Column1 =
VAR _SalesPerson = [Sales Person]
VAR _SalesCategory = [Sales Category]
VAR _ClassDate =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
        ),
        [Column 2]
    )
VAR _maxDateBeforeClassDate =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] < _ClassDate
        ),
        [Latest Sales Date]
    )
VAR _ItemSoldBeforeClass =
    MAXX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] = _maxDateBeforeClassDate
        ),
        [# of Items sold]
    )
VAR _minMonthlyCaptureAfterClassDate =
    MINX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [Latest Sales Date] > _ClassDate
        ),
        [Monthly capture]
    )
VAR _minMonthlyCaptureWhichBiggerBeforeClass =
    MINX (
        FILTER (
            'Monthly Captures',
            [Sales Person] = _SalesPerson
                && [Sales Category] = _SalesCategory
                && [# of Items sold] > _ItemSoldBeforeClass
        ),
        [Monthly capture]
    )
VAR _result =
    SWITCH (
        TRUE (),
        [ClassPresence] = "NO", "Not attending classes",
        [Latest Sales Date] < DATEVALUE ( [ClassDate] ), "Not attending classes",
        ISBLANK ( _maxDateBeforeClassDate )
            && [Monthly capture] = _minMonthlyCaptureAfterClassDate, "Closed the first sale(s) after classes",
        ISBLANK ( _maxDateBeforeClassDate )
            && [Monthly capture] <> _minMonthlyCaptureAfterClassDate, "Increased sales after classes",
        IF (
            ISBLANK ( _minMonthlyCaptureWhichBiggerBeforeClass ),
            "No change after classes",
            SWITCH (
                TRUE (),
                [Monthly capture] < _minMonthlyCaptureWhichBiggerBeforeClass, "No change after classes",
                [Monthly capture] = _minMonthlyCaptureWhichBiggerBeforeClass, "Closed the first sale(s) after classes",
                "Increased sales after classes"
            )
        )
    )
RETURN
    _result

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Chris2016's avatar
      Chris2016
      Resolver I
      2 years ago

      Thanks so much for your help, your solution is great! I was able to tweak your formula to get what I need. 

      Best regards!