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 ,

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.