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

v-sdhruv's avatar
v-sdhruv
Community Support
1 year ago

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