Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
yuvan29
Regular Visitor

Previous 3 day average line graph

Hello,

I need some help in creating a measure that caluclates the average value at a certain time for the past 3 days. In my current line graph, I've got different times of the day on the x-axis and the number of calls on the y-axis. I also have a filter on the visual that selects which day to show the data for.

yuvan29_1-1732147181269.png

For e.g. lets say the line graph above is filtered for the day of 5th November 2024. The datapoint on the average line graph at 9am will look at the number of calls at 9am on the 4th November, number of calls at 9am on the 3rd November and number of calls at 9am on the 2nd November and find the average of these numbers.

 

Appreciate any help I can get, thank you!

2 ACCEPTED SOLUTIONS
v-zhengdxu-msft
Community Support
Community Support

Hi @yuvan29 

 

Measure can only return one value at a date point, so I create 4 measures.

Here's the sample:

Table:

vzhengdxumsft_0-1732155975335.png

Last day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _previousday
			)
		)
Last 2 day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	VAR _last2day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _previousday
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _last2day
			)
		)
Last 3 day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	VAR _last2day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _previousday
		)
	)
    VAR _last3day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _last2day
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _last3day
			)
		)
AVG = 
	VAR _Today = IF(
		'Table'[Today] <> BLANK(),
		1,
		0
	)
	VAR _Previousday = IF(
		'Table'[Last day] <> BLANK(),
		1,
		0
	)
	VAR _Last2day = IF(
		'Table'[Last 2 day] <> BLANK(),
		1,
		0
	)
	RETURN
		([Last 2 day] + [Last day] + [Today]) / (_Last2day + _Previousday + _Today)

The result is as follow:

vzhengdxumsft_2-1732156613798.png

 

 

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.

View solution in original post

FreemanZ
Super User
Super User

Hi @yuvan29 ,

 

Supposing you have a data table like this:

FreemanZ_0-1732169900227.png

 

you can try to plot a visual with time column and a measure like this:

Rolling 3d Avg = 
VAR _date = SELECTEDVALUE(data[date])
VAR _last3d = 
TOPN(
    3,
    FILTER(ALL(data[Date]), data[date]<_date),
    data[date]
)
VAR _3dtotal =
CALCULATE(
    SUM(data[value]),
    _last3d
)
VAR _result = DIVIDE(_3dtotal, COUNTROWS(_last3d))
RETURN _result

 

it worked like this:

FreemanZ_1-1732170030841.png

 

Please find more info in the attached file.

 

p.s.:

1. If more info could be provided, the code could be further simplified, like in the attachment. 

2. With dedicated calendar tables, you will also find Time Intelligence functions like DATESINPERIOD very handy for such cases.

View solution in original post

3 REPLIES 3
FreemanZ
Super User
Super User

Hi @yuvan29 ,

 

Supposing you have a data table like this:

FreemanZ_0-1732169900227.png

 

you can try to plot a visual with time column and a measure like this:

Rolling 3d Avg = 
VAR _date = SELECTEDVALUE(data[date])
VAR _last3d = 
TOPN(
    3,
    FILTER(ALL(data[Date]), data[date]<_date),
    data[date]
)
VAR _3dtotal =
CALCULATE(
    SUM(data[value]),
    _last3d
)
VAR _result = DIVIDE(_3dtotal, COUNTROWS(_last3d))
RETURN _result

 

it worked like this:

FreemanZ_1-1732170030841.png

 

Please find more info in the attached file.

 

p.s.:

1. If more info could be provided, the code could be further simplified, like in the attachment. 

2. With dedicated calendar tables, you will also find Time Intelligence functions like DATESINPERIOD very handy for such cases.

Kedar_Pande
Super User
Super User

@yuvan29 

New Measure

Average Calls Last 3 Days = 
VAR SelectedTime = SELECTEDVALUE('YourTable'[Time])
VAR SelectedDate = SELECTEDVALUE('YourTable'[Date])

RETURN
AVERAGEX(
DATESINPERIOD(
'YourTable'[Date],
SelectedDate,
-3,
DAY
),
CALCULATE(
SUM('YourTable'[NumberOfCalls]),
'YourTable'[Time] = SelectedTime
)
)

Add a Line Chart

Put your Time on the X-axis.

Use the measure Average Calls Last 3 Days for the Y-axis.

 

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

v-zhengdxu-msft
Community Support
Community Support

Hi @yuvan29 

 

Measure can only return one value at a date point, so I create 4 measures.

Here's the sample:

Table:

vzhengdxumsft_0-1732155975335.png

Last day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _previousday
			)
		)
Last 2 day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	VAR _last2day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _previousday
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _last2day
			)
		)
Last 3 day = 
	VAR _currentTime = MAX('Table 2'[Time])
	VAR _today = DATE(YEAR(MAX('Table'[Date\Time])), MONTH(MAX('Table'[Date\Time])), DAY(MAX('Table'[Date\Time])))
	VAR _previousday = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _today
		)
	)
	VAR _last2day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _previousday
		)
	)
    VAR _last3day = CALCULATE(
		MAX('Table'[Date\Time].[Date]),
		FILTER(
			ALL('Table'),
			DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) < _last2day
		)
	)
	RETURN
		CALCULATE(
			SUM('Table'[Values]),
			FILTER(
				ALL('Table'),
				TIME(
					HOUR('Table'[Date\Time]),
					MINUTE('Table'[Date\Time]),
					SECOND('Table'[Date\Time])
				) = _currentTime && DATE(YEAR('Table'[Date\Time]), MONTH('Table'[Date\Time]), DAY('Table'[Date\Time])) = _last3day
			)
		)
AVG = 
	VAR _Today = IF(
		'Table'[Today] <> BLANK(),
		1,
		0
	)
	VAR _Previousday = IF(
		'Table'[Last day] <> BLANK(),
		1,
		0
	)
	VAR _Last2day = IF(
		'Table'[Last 2 day] <> BLANK(),
		1,
		0
	)
	RETURN
		([Last 2 day] + [Last day] + [Today]) / (_Last2day + _Previousday + _Today)

The result is as follow:

vzhengdxumsft_2-1732156613798.png

 

 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Users online (1,548)