Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ibrahim_shaik
Helper IV
Helper IV

Aggregate Hourly Data to Weekly, Monthly and Yearly

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.

 

Weekly Column chart.png

 

Please suggest a solution.

 

Thanks & Regards,

Ibrahim.

5 REPLIES 5
Ibrahim_shaik
Helper IV
Helper IV

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
Super User
Super User

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

danextian_0-1726227264897.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
dk_dk
Super User
Super User

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





bhanu_gautam
Super User
Super User

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

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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.

 

Blank Column.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.