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

Highlighted
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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors