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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors