cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rbbi Helper II
Helper II

Year-to-date total, multi-year, with year as legend

I'm wondering what's the best/easiest way to achieve this ...

YearToDateChart2 - Copy.jpg

i.e. I'd like to have a year-to-date chart (line or column) to compare year-on-year year-to-date totals.

If I just create a standard year-to-date quick measure and plot against date, I get this ...

YearToDateChart.jpg

 

But I'd like have the lines overlaying each other AND I'd like to avoid the highlighted "tail" that goes past the current date (14 July 2018).

One approach that solves the overlay part is I've added an extra date column that adds years to prior year records so that they are in the current year (e.g. 1 July 2017 becomes 1 July 2018), but still use the original year as the legend. Then use this adjusted date for the year-to-date measure. This gets the overlay working, but still has the "tail" ...

YearToDateChart2.jpg

So Im wondering if I'm on the right track here, and how to get rid of future date points.

Measure 1:

Amount YTD =
IF(
ISFILTERED('Expenses'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(SUM('Expenses'[Amount]), 'Expenses'[Date].[Date])
)

 

Measure 2: (using adjusted date)

Amount YTD 2 =
IF(
ISFILTERED(Expenses[Date This Year]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALYTD(SUM('Expenses'[Amount]), Expenses[Date This Year].[Date])
)

 

many thanks!

Rod

www.burkedataconsulting.com

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
rbbi Helper II
Helper II

Re: Year-to-date total, multi-year, with year as legend

ok - I found an answer in this post ...

https://community.powerbi.com/t5/Desktop/YTD-CalendarAuto-exclude-future-sales-dates/m-p/357367#M161...

It gets the job done, but I'm still wondering if this is the optimal solution?

 

The simple steps are:

1. Make a measure for the sum: 

 

Sum of Amount = sum(Expenses[Amount])

 

2. Use this measure in the TOTALYTD and condition it with if(isblank())

NOTE: I think it's the isblank condition that makes the difference ... the YTD sum can optionally still just use the sum of the actual column but using the measure makes sense and makes the code clearer.

 

Amount YTD =

if(ISBLANK([Sum of Amount]), <--- if the sum measure is blank

    BLANK(), <--- return blank

    TOTALYTD([Sum of Amount], Expenses[Date This Year].[Date])) <--- otherwise return the YTD sum

View solution in original post

3 REPLIES 3
Highlighted
rbbi Helper II
Helper II

Re: Year-to-date total, multi-year, with year as legend

ok - I found an answer in this post ...

https://community.powerbi.com/t5/Desktop/YTD-CalendarAuto-exclude-future-sales-dates/m-p/357367#M161...

It gets the job done, but I'm still wondering if this is the optimal solution?

 

The simple steps are:

1. Make a measure for the sum: 

 

Sum of Amount = sum(Expenses[Amount])

 

2. Use this measure in the TOTALYTD and condition it with if(isblank())

NOTE: I think it's the isblank condition that makes the difference ... the YTD sum can optionally still just use the sum of the actual column but using the measure makes sense and makes the code clearer.

 

Amount YTD =

if(ISBLANK([Sum of Amount]), <--- if the sum measure is blank

    BLANK(), <--- return blank

    TOTALYTD([Sum of Amount], Expenses[Date This Year].[Date])) <--- otherwise return the YTD sum

View solution in original post

Community Support
Community Support

Re: Year-to-date total, multi-year, with year as legend

@rbbi,

 

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rbbi Helper II
Helper II

Re: Year-to-date total, multi-year, with year as legend

@MattAllington would you have a chance to share some thoughts on this? I'm trying to get year-on-year year-to-date, dynamically to work with any date range that's in the data.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors