How to represent date ranges in line chart

[justinthobela]

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

[Ashish_Mathur]

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.

[parry2k · Accepted solution]

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

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

[justinthobela]

Thank you so much @parry2k , you made my nigh 🙂