Counting difference in sums between dates grouped by a third value

Hey ChrisR22,

 

defining a single measure that can be used with different data visualizing types, meaning different axis requires a more complex measure and a data mode instead of a single table. For this reason I created a client and a date of entry table using the below DAX statements:

client table

client = DISTINCT( ALLNOBLANKROW( 'Table'[Client] ) ) 

and the date of entry table

date of entry = DISTINCT( ALLNOBLANKROW( 'Table'[Date of Entry] ) )

 From the three tables I created the below semantic model:

Be aware that I use the client column and the date of entry columns from the new tables instead of the columns from the original table, the original table can be considered a fact table in the world of dimensional modeling.

The measure looks like this:

new measure = 
IF( HASONEVALUE( 'date of entry'[Date of Entry] )
    ,SUMX(
        VALUES( 'client'[Client] )
        , var p = 
            SELECTCOLUMNS(
                OFFSET(
                    -1
                    , SUMMARIZE(
                        ALLSELECTED( 'Table' )
                        , 'client'[Client]
                        , 'date of entry'[Date of Entry]
                    )
                    , ORDERBY( 'date of entry'[Date of Entry] , ASC )
                    , DEFAULT
                    , PARTITIONBY( 'client'[Client] )
                )
                , [Date of Entry]
            )
        return
        IF( not( ISBLANK( p ) )
            ,CALCULATE( SUM( 'Table'[Total Document Count] ) )-
            CALCULATE( SUM( 'Table'[Total Document Count] )
                , 'date of entry'[Date of Entry] = p
            )
            , BLANK()
        )
    )
    , SUMX(
        SUMMARIZE(
            'Table'
            , 'client'[Client]
            , 'date of entry'[Date of Entry]
        )
        , var p = 
            SELECTCOLUMNS(
                OFFSET(
                    -1
                    , SUMMARIZE(
                        ALLSELECTED( 'Table' )
                        , 'client'[Client]
                        , 'date of entry'[Date of Entry]
                    )
                    , ORDERBY( 'date of entry'[Date of Entry] , ASC )
                    , DEFAULT
                    , PARTITIONBY( 'client'[Client] )
                )
                , [Date of Entry]
            )
        return
        IF( not( ISBLANK( p ) )
            ,CALCULATE( SUM( 'Table'[Total Document Count] ) )-
            CALCULATE( SUM( 'Table'[Total Document Count] )
                , 'date of entry'[Date of Entry] = p
            )
            , BLANK()
        )
    )
)

Using this measure in combination with the dimension tables a report might look like this:

 

Hopefully, this helps to tackle your challenge.

 

Regards,

Tom