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

Shahid12523's avatar
Shahid12523
Community Champion
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)
)