Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to represent date ranges in line chart

Hi members. Please help.

is it possible to represent ranges in a pie chart? 

Eg, the chart has a SUM value from a measure an i want it to evaluate YearToDate in the chart represented by a year. see image below:


So far the graph is summing per year, but what I need is summing a Year to date Range (2004 - 2004, 2004 - 2005, 2004 - 2006 etc)

Thanks in advance

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous for any time intelligence it is good to have a calendar table in the model and you can create on following my blog post Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

     

    Once the table is in the model and it has a relationship with your transaction table, add the following measure for cumulative total:

     

    Cumulative Total = 
CALCULATE ( 
SUM ( YourTable[YourColumn] ), 
FILTER ( ALLSELECTED( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

     

    In the line chart visual, use the year column from the calendar table and above measure and you will get the output you are looking for.

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much parry2k , you made my nigh 🙂

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    Create a Calendar Table with a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  In the Calendar Table, write a calculated column column formula to extract the Year.  To your visual, drag the year from the Calendar Table.  Write this measure

    Sales = sum(data[amount])

    Sales till date = calculate([sales],datesbetween(Calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]))

    Hope this helps.