Forum Discussion

Syk's avatar
Syk
Icon for Resident Rockstar rankResident Rockstar
3 years ago
Solved

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.


 

Ideas?

 

Need Help
  • Syk's avatar
    Syk
    3 years ago

    I was able to get something pretty close.

    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.

    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]))

     

3 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    I was able to get something pretty close.

    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.

    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]))

     

    • asitm's avatar
      asitm
      Icon for Helper III rankHelper III
      3 years ago

      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's avatar
    asitm
    Icon for Helper III rankHelper III
    3 years ago

    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.

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

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