Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Here's my problem:
I need to know the difference between two numbers based on the time. The first number is the first value recorded at the beginning of the day, the second number is the last value recorded at the end of the day.
Max-Min does not work because during the day there may be lower values than those recorded at the beginning of the day.
Thanks
Solved! Go to Solution.
Thank you ValtteriN
Its not working.
Here is an exemple of my table:
I need to calculate 1860-1658.
This as to work every day. Any ideas?
FECHA | VALOR |
21/12/2021 00:01 | 1658 |
21/12/2021 18:04 | 1826 |
21/12/2021 18:43 | 1902 |
21/12/2021 18:57 | 1861 |
21/12/2021 19:55 | 1926 |
21/12/2021 20:03 | 2002 |
21/12/2021 20:13 | 2036 |
21/12/2021 21:37 | 1912 |
21/12/2021 21:45 | 1802 |
21/12/2021 22:04 | 2098 |
21/12/2021 22:16 | 2074 |
21/12/2021 22:24 | 1940 |
21/12/2021 22:41 | 1717 |
21/12/2021 22:50 | 1675 |
21/12/2021 22:58 | 1830 |
21/12/2021 23:33 | 1734 |
21/12/2021 23:40 | 1476 |
21/12/2021 23:58 | 1860 |
This is easier if you separate your FECHA column into a Date column and a Time column (which is good practice anyway).
If you do this, then you should be able to write a measure like this:
VAR Summary =
ADDCOLUMNS (
SUMMARIZE (
Table1,
Table1[Date],
"MinTime", MIN ( Table1[Time] ),
"MaxTime", MAX ( Table1[Time] )
),
"MinVal", CALCULATE ( SUM ( Table1[VALOR] ), Table1[Time] = EARLIER ( [MinTime] ) ),
"MaxVal", CALCULATE ( SUM ( Table1[VALOR] ), Table1[Time] = EARLIER ( [MaxTime] ) )
)
RETURN
SUMX ( Summary, [MaxVal] - [MinVal] )
Thanks,
still not working...
maybe I explained it wrong.
For the example below, I look for the result of 706 (494.732-494026) obtained on day 20 for the variable 1047
It works fine for me after defining the date and time columns.
You get zeros at the time granularity though since there is only one time in the filter context. This can be changed by removing that filter context.
Can you send me the pbix file?
Thanks
Can you share the data in a format I can copy and paste from?
Hi,
Try something like this:
var _cdate = DATE(YEAR(Table[Time]),MONTH(Table[Time]),DAY(Table[Time]))
var _minTime = Calculate(MIN(Table[Time]),ALL(Table),DATE(YEAR(Table[Time]),MONTH(Table[Time]),DAY(Table[Time]))=_cdate)
var _maxTime =
Calculate(MAX(Table[Time]),ALL(Table),DATE(YEAR(Table[Time]),MONTH(Table[Time]),DAY(Table[Time]))=_cdate)
var _maxvalue =Calculate(MAX(Table[Value]),ALL(Table),Table[Time]=_maxTime)
var _minvalue =Calculate(MAX(Table[Value]),ALL(Table),Table[Time]=_minTime)
return
_maxvalue-_minvalue
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!