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

Line and column chart at different date granularities

Hi everyone!
I'm trying to see if this is possible in Power BI. I have data that is recorded daily that I want to summarize per month with a column. However, I also want to see the daily data. See the screenshot as a quick example.


Syk_1-1684417840812.png

 

Ideas?

 

1 ACCEPTED SOLUTION
Syk
Super User
Super User

I was able to get something pretty close.

Syk_0-1684865276029.png

For this you have to use the 'Date' in the x-axis and NOT the 'Date Heirarchy'. This gives you a zoomed out view of all your days so you don't have the scroll bar on the bottom.

Syk_1-1684865322936.png

Then all thats left is to create a measure that evaluates to your monthly value you'd like to show and set it as your columns. For this one, the DAX was...

Measure = CALCULATE(SUM('Table'[Value]),REMOVEFILTERS('Date_dim'[Date]),VALUES(Date_dim[Year-Month]))

 

View solution in original post

3 REPLIES 3
Syk
Super User
Super User

I was able to get something pretty close.

Syk_0-1684865276029.png

For this you have to use the 'Date' in the x-axis and NOT the 'Date Heirarchy'. This gives you a zoomed out view of all your days so you don't have the scroll bar on the bottom.

Syk_1-1684865322936.png

Then all thats left is to create a measure that evaluates to your monthly value you'd like to show and set it as your columns. For this one, the DAX was...

Measure = CALCULATE(SUM('Table'[Value]),REMOVEFILTERS('Date_dim'[Date]),VALUES(Date_dim[Year-Month]))

 

That's pretty smart. This does add more data points to the plots though. Hopefully your date range is not big otherwise it will impact the rendering of the entire report.

asitm
Helper III
Helper III

Hi @Syk 

This visual is possible with our GUI based Editor tool - PBIVizEdit. Below is the viz you needed. I have attached the .pbix and .pbiviz in case you need to refer those. If you want to tinker around for settings let me know and I can share the editor template for this visual (for any further modification to the visual) with you.

The visual is free and without any limitations on Desktop.

asitm_0-1684500495712.png

https://drive.google.com/file/d/10jrwebLPy40QI0CLauSW3MvTgNZzeEPB/view?usp=sharing

https://drive.google.com/file/d/1i16jJ9OcxPdxnU_Dj0HH18dD7ZzMwyEn/view?usp=sharing

 

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.