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

"DAX help" 'Need help' 'DAX'
  • Shahid12523's avatar
    Shahid12523
    11 months 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)
    )

10 Replies

  • 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.

  • 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.

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    11 months 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 ,

    Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months 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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    11 months ago

    Hi Ibrahim_shaik ,

    Can you please mark it as a solution so that other users can also benefit from it.
    Thank you for using Microsoft Community Forum