Summarize only the latest data using Tracking Time

[amar_raj]

Refer the Sample data below:

DT	Transaction_Time	Tracking_Time	ID	Value1	Value2	Value3	Type
05/07/19	05/06/19 1:00	05/06/19 1:00	1	5	6	7	E
05/07/19	05/06/19 1:00	05/06/19 2:00	1	10	12	14	E
05/07/19	05/06/19 1:00	05/06/19 3:00	1	15	18	21	E
05/07/19	05/06/19 1:00	05/06/19 4:00	1	20	24	28	V
05/07/19	05/06/19 13:00	05/06/19 13:30	2	25	30	35	V
05/07/19	05/06/19 13:00	05/06/19 14:30	2	30	36	42	V
05/07/19	05/06/19 13:00	05/06/19 15:30	2	35	42	49	X
05/07/19	05/06/19 20:00	05/06/19 20:15	3	40	48	56	X
05/07/19	05/06/19 20:00	05/06/19 20:45	3	45	54	63	E
05/08/19	05/06/19 1:00	05/07/19 5:00	1	50	60	70	E
05/08/19	05/06/19 13:00	05/07/19 4:00	2	55	66	77	X
05/08/19	05/06/19 20:00	05/07/19 3:00	3	60	72	84	E
05/08/19	05/06/19 1:00	05/07/19 6:00	1	65	78	91	V
05/08/19	05/06/19 20:00	05/07/19 4:00	3	70	84	98	V
05/09/19	05/06/19 1:00	05/08/19 7:00	1	75	90	105	E
05/09/19	05/06/19 20:00	05/08/19 5:00	3	80	96	112	V
05/09/19	05/06/19 13:00	05/08/19 5:00	2	85	102	119	X
05/09/19	05/06/19 13:00	05/08/19 6:00	2	90	108	126	E
05/09/19	05/06/19 20:00	05/08/19 6:00	3	95	114	133	V
05/09/19	05/06/19 1:00	05/08/19 8:00	1	100	120	140	X

Report has a Tracking Time slider, so when the slider is set to 05/06/2019 to 05/08/2019 the latest record based on the tracking time should be as follows. I want only the latest record from this table and summarize them based on Number and Type:

For e.g. For Tracking Time 05/06/2019 to 05/08/2019

 For ID 1 the latest record is the last row based on the Tracking Time. Similarly… I want to summarize this table into this first in the background as follows:

Table 1

DT	Transaction_Time	Tracking_Time	ID	Value1	Value2	Value3	Type
05/09/19	05/06/19 1:00	05/08/19 8:00	1	100	120	140	X
05/09/19	05/06/19 13:00	05/08/19 6:00	2	90	108	126	E
05/09/19	05/06/19 20:00	05/08/19 6:00	3	95	114	133	V

Now, based on the above table 1->A summary table/matrix Visualization should be created as follows:

Type	Count	Value1	Value2	Value3
V	1	95	114	133
E	1	90	108	126
X	1	100	120	140
Total	3	285	342	399

Similarly if I change the Tracking to a time period from 05/06/2019 to 05/07/2019

Table 2

DT	Transaction_Time	Tracking_Time	ID	Value1	Value2	Value3	Type
05/08/19	05/06/19 1:00	05/07/19 6:00	1	65	78	91	V
05/08/19	05/06/19 13:00	05/07/19 4:00	2	55	66	77	X
05/08/19	05/06/19 20:00	05/07/19 4:00	3	70	84	98	V

In other words, it should be Dynamic:

Now based on the above table 2 ->A summary table/matrix Visualization should be created as follows:

Type	Count	Value1	Value2	Value3
V	2	65+70	78+84	91+98
X	1	55	66	77
Total	3	190	228	266

There are millions of records generated everyday, so it should be well optimized

I am attaching a sample report to go with it.

I’ve figured out the Total Part, its just that the rows based on the for each type give inconsistent Data.

Report File.pbix

[v-frfei-msft]

Hi @amar_raj ,

Firstly, we should create a calculated table as below and use the column of the new table as a slicer.

tracking = DISTINCT(Table1[Tracking_Time])

To create measures as below.

if = 
VAR maxd =
    FORMAT( MAX ( 'tracking'[Tracking_Time] ),"yyyymmdd")
VAR d =
    FORMAT(MAX ( Table1[Tracking_Time] ),"yyyymmdd")
VAR maxxd =
    CALCULATE (
        MAX ( 'Table1'[Tracking_Time] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            'Table1'[Tracking_Time] <= MAX ( 'tracking'[Tracking_Time] )
        ),
        VALUES(Table1[ID])
    )
RETURN
    IF ( maxd = d && MAX ( 'Table1'[Tracking_Time] ) = maxxd, 1, 0 )

s1 = 
VAR valuesss =
    CALCULATETABLE (
        DISTINCT ( Table1[Type] ),
        FILTER ( ALLSELECTED ( Table1 ), [if] = 1 )
    )
RETURN
    IF ( MAX ( Table1[Type] ) IN valuesss, SUMX ( Table1, [sv1] ), BLANK () )

s2 = 
VAR valuesss =
    CALCULATETABLE (
        DISTINCT ( Table1[Type] ),
        FILTER ( ALLSELECTED ( Table1 ), [if] = 1 )
    )
RETURN
    IF ( MAX ( Table1[Type] ) IN valuesss, SUMX ( Table1, [SV2] ), BLANK () )

s3 = 
VAR valuesss =
    CALCULATETABLE (
        DISTINCT ( Table1[Type] ),
        FILTER ( ALLSELECTED ( Table1 ), [if] = 1 )
    )
RETURN
    IF ( MAX ( Table1[Type] ) IN valuesss, SUMX ( Table1, [SV3] ), BLANK () )

sv1 = IF([if]=1,MAX(Table1[Value1]),BLANK())

SV2 = IF([if]=1,MAX(Table1[Value2]),BLANK())

SV3 = IF([if]=1,MAX(Table1[Value3]),BLANK())

[amar_raj]

Hi @v-frfei-msft , thank you for your effort, but the as you can see the total is correct but indivdual Type by S1 values dont match:

Type	s1	s2	s3
E	165	198	231
V	95	114	133
X	185	222	259
Total	285	342	399

Every ID should be computed only once, for whatever its latest record is and compute it for that type only

It should be something like this:

Type	Count of ID	s1	s2	s3
X	1	100	120	140
E	1	90	108	126
V	1	95	114	133
Total	3	285	342	399

Thank You