Dynamic Average per month Calculation

[Ibrahim_shaik]

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.   

[v-sdhruv]

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

[v-sdhruv]

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

[Ibrahim_shaik]

Hi @v-sdhruv @Shahid12523 ,

Apologies for late response.

Thank you for providing the solution @Shahid12523 

My query is resolved.

[Shahid12523 · Accepted solution]

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

[v-sdhruv]

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

[v-sdhruv]

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

[sreejad]

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.

[Ibrahim_shaik]

Hi @sreejad ,

Thank you for the solution.

[bhanu_gautam]

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

[Ibrahim_shaik]

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.