Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
See my sample data below. I want to get the last value of the previous day. So for example, I need to get the last value of 19/8 which is 19/8 22:45 i.e. 7264906.5. How do I achieve using a measure?
19/8/2019 22:30 7345461.5 7264907 19/8/2019 22:31 7345461.5 7264907 19/8/2019 22:32 7345461.5 19/8/2019 22:33 7345461.5 7264907 19/8/2019 22:34 7345461.5 7264907 19/8/2019 22:35 7345461.5 7264907 19/8/2019 22:36 7345461.5 7264907 19/8/2019 22:37 7345461.5 7264907 19/8/2019 22:38 7345461.5 7264907 19/8/2019 22:39 7345461.5 7264907 19/8/2019 22:40 7345461.5 7264907 19/8/2019 22:41 7345461.5 7264907 19/8/2019 22:42 7345461.5 19/8/2019 22:43 7345461.5 7264907 19/8/2019 22:44 7345461.5 7264906.5 19/8/2019 22:45 7345461.5 7264906.5 20/8/2019 0:00 7345461.5 7264904 20/8/2019 0:01 7345461.5 7264904 20/8/2019 0:02 7345461.5 7264904 20/8/2019 0:03 7345461.5 7264903.5 20/8/2019 0:04 7345461.5 7264903.5
Solved! Go to Solution.
Hi Kolumam,
My sample:
dateamountid
19/8/2019 22:30 | 7345461.5 | 7264907 |
19/8/2019 22:31 | 7345461.5 | 7264907 |
19/8/2019 22:32 | 7345461.5 | |
19/8/2019 22:33 | 7345461.5 | 7264907 |
19/8/2019 22:34 | 7345461.5 | 7264907 |
19/8/2019 22:35 | 7345461.5 | 7264907 |
19/8/2019 22:36 | 7345461.5 | 7264907 |
19/8/2019 22:37 | 7345461.5 | 7264907 |
19/8/2019 22:38 | 7345461.5 | 7264907 |
19/8/2019 22:39 | 7345461.5 | 7264907 |
19/8/2019 22:40 | 7345461.5 | 7264907 |
19/8/2019 22:41 | 7345461.5 | 7264907 |
19/8/2019 22:42 | 7345461.5 | |
19/8/2019 22:43 | 7345461.5 | 7264907 |
19/8/2019 22:44 | 7345461.5 | 7264906.5 |
19/8/2019 22:45 | 7345461.5 | 7264906.5 |
20/8/2019 0:00 | 7345461.5 | 7264904 |
20/8/2019 0:01 | 7345461.5 | 7264904 |
20/8/2019 0:02 | 7345461.5 | 7264904 |
20/8/2019 0:03 | 7345461.5 | 7264903.5 |
20/8/2019 0:04 | 7345461.5 | 7264903.5 |
You could try below measure (because today is 8/21, so it will sjow 8/20 result, if you want to see 8/19, you could use today()-2)
Measure 5 = VAR maxd = CALCULATE ( MAX ( tets[date] ), FILTER ( ALL ( tets ), tets[date].[Date] <= TODAY () - 1 ) ) RETURN CALCULATE ( MIN ( tets[id] ), FILTER ( tets, tets[date] = maxd ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Kolumam,
My sample:
dateamountid
19/8/2019 22:30 | 7345461.5 | 7264907 |
19/8/2019 22:31 | 7345461.5 | 7264907 |
19/8/2019 22:32 | 7345461.5 | |
19/8/2019 22:33 | 7345461.5 | 7264907 |
19/8/2019 22:34 | 7345461.5 | 7264907 |
19/8/2019 22:35 | 7345461.5 | 7264907 |
19/8/2019 22:36 | 7345461.5 | 7264907 |
19/8/2019 22:37 | 7345461.5 | 7264907 |
19/8/2019 22:38 | 7345461.5 | 7264907 |
19/8/2019 22:39 | 7345461.5 | 7264907 |
19/8/2019 22:40 | 7345461.5 | 7264907 |
19/8/2019 22:41 | 7345461.5 | 7264907 |
19/8/2019 22:42 | 7345461.5 | |
19/8/2019 22:43 | 7345461.5 | 7264907 |
19/8/2019 22:44 | 7345461.5 | 7264906.5 |
19/8/2019 22:45 | 7345461.5 | 7264906.5 |
20/8/2019 0:00 | 7345461.5 | 7264904 |
20/8/2019 0:01 | 7345461.5 | 7264904 |
20/8/2019 0:02 | 7345461.5 | 7264904 |
20/8/2019 0:03 | 7345461.5 | 7264903.5 |
20/8/2019 0:04 | 7345461.5 | 7264903.5 |
You could try below measure (because today is 8/21, so it will sjow 8/20 result, if you want to see 8/19, you could use today()-2)
Measure 5 = VAR maxd = CALCULATE ( MAX ( tets[date] ), FILTER ( ALL ( tets ), tets[date].[Date] <= TODAY () - 1 ) ) RETURN CALCULATE ( MIN ( tets[id] ), FILTER ( tets, tets[date] = maxd ) )
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kolumam
You don't describe how you want to use the measure so I'll conjecture.
1. Since you are looking at day level and you have timestamps on the table, create a calculated column with only the day and convert to type date. Table1[TimeStamp] is the first column you show:
Day = INT(Table1[TimeStamp])
2. Place Table1[Day] in the rows of a matrix visual
3. Create this measure and place it in the matrix visual
Measure = VAR LastTimePreviousDay = CALCULATE ( MAX ( Table1[TimeStamp] ), FILTER ( ALL ( Table1[Day] ), Table1[Day] < SELECTEDVALUE ( Table1[Day] ) ) ) RETURN CALCULATE ( DISTINCT ( Table1[Value] ), FILTER ( ALL ( Table1[Day] ), Table1[TimeStamp] = LastTimePreviousDay ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |