Forum Discussion
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
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?
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