Counting difference in sums between dates grouped by a third value

[ChrisR22]

Hello!

I have the below data, updated weekly by adding a new row with the client's current document count.

Client	Date of Entry	Total Document Count
Apple	11/23/23	11560
Orange	11/23/23	5439
Banana	11/23/23	8764
Apple	12/1/23	12000
Orange	12/1/23	5520
Banana	12/1/23	9000
Apple	12/7/23	12600
Orange	12/7/23	5529
Banana	12/7/23	9590

What I would like to do is be able to count the number of documents added (or subtracted) each week for each client (ie. the change in the total count between each date, for each client). 

This will be useful so that I can manipulate it with a date slicer and get a sense of, between two dates, how many documents were added or subtracted for each client.

My guess is this is done with a measure, but I am not quite sure how to achieve this. 

Any and all suggestions are greatly appreciated!

[ChrisR22]

@TomMartens Fantastic! I figured, but wanted to clarify mostly for my own education.

And if I were looking to display this delta value, so that when I filter by a certain client and by the date range, it shows me that change in document count, how would I do that? 

I tried putting the measure value in a card, but it just keeps returning the total value, not the change between the two dates when I set my slicer to have two of the dates in the table.

[TomMartens]

Hey @ChrisR22 ,

it's because I use ALLSELECTED.

Pause, breathe, focus ... describe what you really want. Not every measure works with every visual because the implicit filter context like categorical axis, row/headers are missing.

You also have to keep in mind that some DAX challenges require a data model that adheres to the principals of dimensional modeling. The "dreaded" one-table  solutions seem simple in the beginning but most often become a limiting factor, performance-wise but also in regards to the complexity of challenges that can be solved using DAX: https://www.sqlbi.com/articles/understanding-dax-auto-exist/#:~:text=The%20auto%2Dexist%20mechanism%....

Regards,

Tom

Regards,

Tom

[ChrisR22]

Understood. What I am looking for is the following:

- a count document creation/deletion (in card or other visual form) of the full client list over the entire timeframe of the data (but can be filtered by both date range and client)

- date slicer to be able to view the creation/deletion within a specific timeframe

- client slicer to be able to select a specific client to see their creation/deletion within a specific timeframe or over the full timeframe

I do believe that is the complete ask, together with the facts about the data as presented in the initial post.

I'm not sure if this might be better achieved by creating a column instead of a measure, similar to what you displayed in your first response. But in that case we'd need to remove the first data point for each client because prior to data collection we don't want to assume that the initial amount was created all at once. 

Does that help?

[TomMartens · Accepted solution]

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

[ChrisR22]

@TomMartens thanks for the quick response. I see that in the offset function, the delta is set at -1. Does this mean that the table would have to remain sorted by client and then date descending in order for the measure to work properly (that is, pull the data from the previous sum for that client, rather than another client)?

[TomMartens]

Hey @ChrisR22 ,

no, the table does not need to be sorted. This is the magic of the Windowing functions.

Offset creates a table that will be used to create a filter table.
The relation SUMMARIZED( ALLSELECTED( ... ) ) is partitioned by the client, the rows inside this partition are ordered by the date column, then the offset -1 "navigates" one step "to the left" from current date perspective.

For Orange/'12/1/2023 Offset returns
Orange/'11/23/2023'

The order of columns or the ordering of rows in the table visual does NOT affect the result of the measure - the magic of windowing functios!

Regards,

Tom

[TomMartens]

Hey @ChrisR22 ,

this measure

CALCULATE( SUM('Table (2)'[Total Document Count] ) )
- 
CALCULATE(
    SUM( 'Table (2)'[Total Document Count] )
    , OFFSET(
        -1
        , SUMMARIZE(
            ALLSELECTED( 'Table (2)' )
            , 'Table (2)'[Client]
            , 'Table (2)'[Date of Entry]
        )
        , ORDERBY( 'Table (2)'[Date of Entry] , asc)
        , DEFAULT
        , PARTITIONBY( 'Table (2)'[Client] )
    )
)

seems to create what you are looking for.
A simple table visual:

The secret is one of the DAX windowing functions: OFFSET

Hopefully, this helps to tackle your challenge.

Regards,

Tom