Calculate sum and deltas for the min and max dates of the range slicer (I have a sample data)

[marijane21]

Hi everyone,
I have this table with time on rows and dates on columns. The dates are selected from the range slicer. The measure is just a sum.
Measure=sum(#)

What I want to do is only display the date of the sum from the min and max of the date range and have a delta measure that calculates the difference between the time and next time interval of the same date (delta1) and also the difference between the dates for each time (delta 2) The time is supposed to be in intervals of 30 min but sometimes the values of the measure do not occur at the same time for both dates. In that case, I want to display the corresponding value.

Example:
imagine that the minimum of the range selected is day 17-01-2024
time      sum
7:00:00  40
7:31:00  60

and the maximum of the range selected is 20-01-2024
time     sum
7:01:00 56
7:30:00 86

I want to display for instance
*from the last value for the prior day

day	17/01	17/01	20/01	20/01	20/01- 17/01
time	sum	delta 1	sum	delta 1	delta 2
7:00:00	40	0*	56	2*	16
7:31:00	60	20	86	30	26

The time picked from the date is not important. I want only one time for intervals of 30 min and the values of the measures in a corresponding order like in the example above.
The ideal goal is doing this in "direct query"  (I have a pro license so I cannot refresh it in 30 min) But I am ok with both scenarios (import and direct query)
I have a sample with some data but I am still working on the measures PBI Sample
I hope that I explained clearly 🙂 Feel free to ask.

Thank you so much for your time!!!

[marijane21]

Wait.. what? No, it is not what I meant. I want to choose 2 dates in a calendar and make these calculations. In power bi desktop we don't have a date picker so I had to use a relative date range slicer and I need to only show the minimum and maximum of that range to perform these calculations. Is there any way to do this?

[marijane21]

Thank you so much! @Anonymous 
do you know how can I just show the minimum and max of the range slicer in the table?
I tried this and put it as a filter on the table but it didn't work

filter_max_min_date =
var min_= CALCULATE(MIN('Table'[Date]), ALLSELECTED('Data'))
var max_=CALCULATE(MAX('Table'[Date]), ALLSELECTED('Data'))

return IF(SELECTEDVALUE('Table'[Date])=min_ || SELECTEDVALUE('Table'[Date])=max_,1,0)

Best regards

[Anonymous]

Hi @marijane21 

Maybe you can just use max() or min() without allselected():

maxdate = MAX('Table'[Date])

mindate = MIN('Table'[Date])

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @marijane21 

Please try this:

Delta 1 = 
	VAR _currentDate = MAX('Table'[Date])
	VAR _currentTime = MAX('Table'[Time])
	VAR _previousTime = CALCULATE(
		MAX('Table'[Time]),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Time] < _currentTime && 'Table'[Date] = _currentDate
		)
	)
	RETURN
		IF(
			_previousTime <> BLANK(),
			SUM('Table'[#]) - CALCULATE(
				[Sum],
				'Table'[Time] = _previousTime
			)
		)

Delta 2 = 
	VAR _currentDate = MAX('Table'[Date])
	VAR _previousDate = CALCULATE(
		MAX('Table'[Date]),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Date] < _currentDate
		)
	)
	RETURN
		IF(
			_previousDate <> BLANK(),
			[Sum] - CALCULATE(
				[Sum],
				'Table'[Date] = _previousDate
			)
		)

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.