Increased, Decreased, No Change 04-09-2024 12:00 PM - last edited 04-11-2024 07:44 AM

[Greg_Deckler]

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