Determine user progress in historical dataset

[Chris2016]

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 · Accepted solution]

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]

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

Best regards!

[lbendlin]

Sample file is not usable

[Chris2016]

Thanks for letting me know, I changed the sensitivity label. Is is usable now? https://fromsmash.com/Historic-data-insights2

Best regards!