Forum Discussion

Ibrahim_shaik's avatar
Ibrahim_shaik
Helper V
1 year ago
Solved

Dynamic Average per month Calculation

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 diffe...
"DAX help" 'Need help' 'DAX'
  • Shahid12523's avatar
    Shahid12523
    1 year ago

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

sreejad's avatar
sreejad
Frequent Visitor
1 year ago

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.