topic Re: Dynamic Average per month Calculation in DAX Commands and Tips

Dynamic Average per month Calculation

Ibrahim_shaik — Wed, 06 Aug 2025 11:29:53 GMT

Hi Power Bi Community,

I have a Power Usage column(hourly data), date table from last Year till today and I want to calculate the daily average power for a month and every month will have a different average and when i select any day or multiple days from the current month or previous month from the date slicer it should return that particular month last year daily average value. User can select 1 day or 2 day or week, month, Year depending on the selection it should return the average for that period and if the user selects one day then also it should return same month last year daily average.

For Instance if i select 5th Aug 25 then it should return same month last year daily average.

Daily Average Calculation = SUM(Individual days Power)/Total no.of days.

Please let me know how i can achieve this.

Thanks & Regards,

Ibrahim.

Re: Dynamic Average per month Calculation

bhanu_gautam — Wed, 06 Aug 2025 12:09:07 GMT

Ibrahim_shaik

Ensure you have a separate date table in your model. T

Make sure your date table is related to your power usage data table through the date column.

You can create a measure to calculate the daily average power usage for the selected period.

DAX
DailyAveragePower =
VAR SelectedDays = COUNTROWS(VALUES('DateTable'[Date]))
RETURN
IF(
SelectedDays > 0,
DIVIDE(SUM('PowerUsageTable'[PowerUsage]), SelectedDays),
BLANK()
)

Create a Measure for Last Year's Daily Average: This measure will calculate the daily average for the same month in the previous year.

LastYearDailyAveragePower =
VAR CurrentMonth = MONTH(SELECTEDVALUE('DateTable'[Date]))
VAR CurrentYear = YEAR(SELECTEDVALUE('DateTable'[Date]))
VAR LastYear = CurrentYear - 1
RETURN
CALCULATE(
[DailyAveragePower],
FILTER(
ALL('DateTable'),
MONTH('DateTable'[Date]) = CurrentMonth &&
YEAR('DateTable'[Date]) = LastYear
)
)

Re: Dynamic Average per month Calculation

sreejad — Fri, 08 Aug 2025 09:17:12 GMT

Hi Ibrahim_shaik

Assuming you have a claendar table and relationship with corresponding fact table.

Previous Year Average = var selecteddates=values('Calendar'[Date]) var preYeardates=SELECTCOLUMNS(ADDCOLUMNS('Calendar',"prevyerdate",DATE(year('Calendar'[Date])-1,MONTH('Calendar'[Date]),DAY('Calendar'[Date]))),"Date",[prevyerdate]) return CALCULATE(DailyAverage,TREATAS(preYeardates,'Calendar'[Date]))

DailyAverage is nothing but the formula you already mentioned.

Daily Average Calculation = SUM(Individual days Power)/Total no.of days.

Hope this works.

Thanks.

Re: Dynamic Average per month Calculation

v-sdhruv — Wed, 13 Aug 2025 10:12:48 GMT

Hi Ibrahim_shaik ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You

Re: Dynamic Average per month Calculation

Ibrahim_shaik — Wed, 13 Aug 2025 12:54:25 GMT

Hi bhanu_gautam , v-sdhruv

Sorry for the late response.

Thank you so much for providing the solution. I will try this measure and will let you know.

Thank you once again.

Re: Dynamic Average per month Calculation

Ibrahim_shaik — Wed, 13 Aug 2025 12:56:53 GMT

Hi sreejad ,

Thank you for the solution.

Re: Dynamic Average per month Calculation

v-sdhruv — Wed, 20 Aug 2025 07:11:44 GMT

Hi @Ibrahim_shaik ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You

Re: Dynamic Average per month Calculation

Shahid12523 — Fri, 22 Aug 2025 03:52:35 GMT

To calculate daily average power usage for the same month last year based on any date selection:
Detect the selected date(s).
Shift to the same month in the previous year.
Sum power usage for that month.
Divide by number of days in that month.

DailyAvgPower_LastYearMonth =
VAR SelectedMonth = MONTH(MIN('DateTable'[Date]))
VAR SelectedYear = YEAR(MIN('DateTable'[Date])) - 1
VAR DatesLastYear = FILTER(
ALL('DateTable'),
MONTH('DateTable'[Date]) = SelectedMonth &&
YEAR('DateTable'[Date]) = SelectedYear
)
RETURN
DIVIDE(
CALCULATE(SUM(PowerUsage[Usage]), DatesLastYear),
CALCULATE(DISTINCTCOUNT('DateTable'[Date]), DatesLastYear)
)

Re: Dynamic Average per month Calculation

v-sdhruv — Mon, 25 Aug 2025 11:13:35 GMT

Hi Ibrahim_shaik ,

I hope your query got resolved. Thank you Shahid12523 for sharing your input.
We would be closing this thread, if you still need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

Re: Dynamic Average per month Calculation

Ibrahim_shaik — Mon, 25 Aug 2025 11:39:11 GMT

Hi v-sdhruv Shahid12523 ,

Apologies for late response.

Thank you for providing the solution Shahid12523

My query is resolved.

Re: Dynamic Average per month Calculation

v-sdhruv — Mon, 25 Aug 2025 11:42:14 GMT

Hi Ibrahim_shaik ,

Can you please mark it as a solution so that other users can also benefit from it.
Thank you for using Microsoft Community Forum