calculate the value between two date as max an min

[Anonymous]

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

[Anonymous]

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

[AlexisOlson]

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

[Anonymous]

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

[AlexisOlson]

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.

[Anonymous]

Can you send me the pbix file?

Thanks

[AlexisOlson · Accepted solution]

Sure. See attached.

[AlexisOlson]

Can you share the data in a format I can copy and paste from?

[Anonymous]

Here's my pbix file:

PBIX File 

[ValtteriN]

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!