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

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
    Ibrahim_shaik
    Helper V
    1 year ago

    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.