cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Summarize only the latest data using Tracking Time

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

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Summarize only the latest data using Tracking Time

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())

Capture.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
New Member

Re: Summarize only the latest data using Tracking Time

Hi @v-frfei-msft , thank you 

Types1s2s3
E165198231
V95114133
X185222259
Total285342399

 

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:

TypeCount of IDs1s2s3
X1100120140
E190108126
V195114133
Total3285342

399

Thank You

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors