topic Re: Determine user progress in historical dataset in DAX Commands and Tips

Determine user progress in historical dataset

Chris2016 — Tue, 09 Jul 2024 06:50:07 GMT

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!

Re: Determine user progress in historical dataset

lbendlin — Mon, 08 Jul 2024 22:42:11 GMT

Sample file is not usable

Re: Determine user progress in historical dataset

Chris2016 — Tue, 09 Jul 2024 06:51:20 GMT

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

Best regards!

Re: Determine user progress in historical dataset

Anonymous — Tue, 09 Jul 2024 07:46:40 GMT

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.

Re: Determine user progress in historical dataset

Chris2016 — Wed, 10 Jul 2024 14:00:43 GMT

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

Best regards!