Aggregate Hourly Data to Weekly, Monthly and Yearly

[Ibrahim_shaik]

Hello Power Bi Community,

I have hour data and when i drill up the Stacked Column Chart i can see per day and it is aggregated automatically but i also want weekly sum and monthly sum, yearly sum.

I have attached a Screenshot below for clear understanding.

Here when i select week in the time slicer filter it shows 7 days individually i want the total of 7 days same for month and year as well.

Please suggest a solution.

Thanks & Regards,

Ibrahim.

[Ibrahim_shaik]

Hi,

Thank you @danextian , @dk_dk , @bhanu_gautam 

I will apply the suggested solutions and will let you know if i face any issue.

[danextian]

Hi @Ibrahim_shaik ,

You need to use a separate dates/calendar table that columns for year, month, etc and relate that to your fact table. If your fact has datetime, convert that to date first in Power Query by changing the data type. You can easily switch between granularities by using field parameters. Pleasee see attached sample pbix

[dk_dk]

Hi @Ibrahim_shaik 

You should be able to create Year, Month, Week, columns with DAX, based on your original Date column, add those to a hierarchy and add the hierarchy to the axis. The aggregates should then work correctly with the drilldown on the column chart.

Here is an overview of date/time functions to help create the columns you need:

https://learn.microsoft.com/en-us/dax/date-and-time-functions-dax

Hope this helps!

Best,

Daniel

[bhanu_gautam]

@Ibrahim_shaik Ensure you have a date table in your model. If not, create one using DAX:

DateTable = CALENDAR(MIN(YourTable[Date]), MAX(YourTable[Date]))

Add calculated columns to your date table for week, month, and year.

Year = YEAR(DateTable[Date])
Month = FORMAT(DateTable[Date], "MMMM")
Week = WEEKNUM(DateTable[Date])

Create measures to calculate the sum for weekly, monthly, and yearly data.

WeeklySum = CALCULATE(SUM(YourTable[Value]), DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -7, DAY))
MonthlySum = CALCULATE(SUM(YourTable[Value]), DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -1, MONTH))
YearlySum = CALCULATE(SUM(YourTable[Value]), DATESINPERIOD(DateTable[Date], MAX(DateTable[Date]), -1, YEAR))

Add a slicer to your report for selecting the time period (week, month, year). Ensure it is connected to your date table.

Adjust Visuals: Use the created measures in your visuals. For example, in your stacked column chart, use the WeeklySum, MonthlySum, and YearlySum measures to display the aggregated data.

[Ibrahim_shaik]

Hi @bhanu_gautam ,

I have added the week, month measure to the chart and it works but when i select all the months in the slicer a blank column gets added at the start of the x axis.

What is the reason for the Blank column and how can i remove it.