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

[rbbi]

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

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

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

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

[rbbi]

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

[rbbi · Accepted solution]

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

[v-chuncz-msft]

@rbbi,

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