topic calculate the value between two date as max an min in DAX Commands and Tips

calculate the value between two date as max an min

Anonymous — Thu, 27 Jan 2022 18:23:18 GMT

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

Re: calculate the value between two date as max an min

ValtteriN — Thu, 27 Jan 2022 18:37:41 GMT

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!

Re: calculate the value between two date as max an min

Anonymous — Thu, 27 Jan 2022 20:52:19 GMT

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

Re: calculate the value between two date as max an min

AlexisOlson — Thu, 27 Jan 2022 21:37:30 GMT

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

Re: calculate the value between two date as max an min

Anonymous — Fri, 28 Jan 2022 16:22:15 GMT

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

Re: calculate the value between two date as max an min

AlexisOlson — Fri, 28 Jan 2022 16:28:25 GMT

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

Re: calculate the value between two date as max an min

Anonymous — Mon, 31 Jan 2022 13:02:00 GMT

Here's my pbix file:

PBIX File

Re: calculate the value between two date as max an min

AlexisOlson — Mon, 31 Jan 2022 16:13:25 GMT

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.

Re: calculate the value between two date as max an min

Anonymous — Mon, 31 Jan 2022 16:53:27 GMT

Can you send me the pbix file?

Thanks

Re: calculate the value between two date as max an min

AlexisOlson — Mon, 31 Jan 2022 17:12:50 GMT

Sure. See attached.