Increased, Decreased, No Change

Greg_Deckler — Thu, 11 Apr 2024 14:44:06 GMT

Came out of this post here: Re: Looking for help with measures please - Microsoft Fabric Community

Basically, given a set of date-series data, how many customers have increased their score, descreased their score, or remained unchanged from their first data point to their last data point. Also calculates the percentage that have increased, decreased, or remained the same as well as the average number of data points for customers that have increased, decreased, or remained the same. The measures below are for increases, the other measures are nearly identical and are in the attached PBIX file.

Updated to include a variation that excludes customers with only 1 appointment.

Total Icreased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __Result = COUNTROWS( FILTER( __Table, [__Diff] > 0 ) ) RETURN __Result

Total Percent Increased Measure = DIVIDE( [Total Icreased Measure], COUNTROWS(DISTINCT('Table'[Customer ID])), 0)

Average Appointments Increased Measure = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( DISTINCT( 'Table'[Customer ID] ), "__FirstDateScore", VAR __CustomerID = [Customer ID] VAR __FirstDate = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __FirstScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __FirstDate ), [Score] ) RETURN __FirstScore, "__LastDateScore", VAR __CustomerID = [Customer ID] VAR __LastDate = MAXX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID ), [Date] ) VAR __LastScore = MINX( FILTER( ALLSELECTED( 'Table'), [Customer ID] = __CustomerID && [Date] = __LastDate ), [Score] ) RETURN __LastScore ), "__Diff", [__LastDateScore] - [__FirstDateScore] ) VAR __IncreasedCustomers = DISTINCT( SELECTCOLUMNS( FILTER( __Table, [__Diff] > 0 ), "__ID", [Customer ID] ) ) VAR __Result = AVERAGEX( SUMMARIZE( FILTER( 'Table', [Customer ID] IN __IncreasedCustomers ), [Customer ID], "__Count", COUNTROWS('Table') ), [__Count] ) RETURN __Result

eyJrIjoiZmYxMTUxYjUtMTg2Ni00OTcyLWFkMzQtMmY0ZDBhMmYzMTFkIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

topic Increased, Decreased, No Change in Quick Measures Gallery

Increased, Decreased, No Change