YTD vs Full Year

DAX DAX Calculations
1

In my attached PBIX sample file, I’ve made up some random data for dates beginning 1/1/2017 through today. I’m trying to show cumulative amounts for the last two years alongside YTD amounts for this year. In my sample, I’m seeing that, but the YTD line shows flat through the end of this year. I need it to stop at the end of the last full month while still showing the lines for the full year on the previous years’ lines.

What I see now…

image
image958×599 31.1 KB

What I want to see…

image
image958×599 48.2 KB

I hope that makes sense. Any thoughts on how best to accomplish this?

YTD Test.pbix (135.6 KB)

2

Hi @DaveC,

The only way I can think of is dropping the YTD TY line to 0 from March because that is the last month that contains values. Then make a constant black line on value 0 to hide the YTD TY line where it’s 0. It’s not the most elegant way I think, but maybe it’s useful.

Drop line
Drop line1037×628 35.6 KB

solid

Hope is helps.

Daniel

3

Hi Dave,

I’ve added a “CompletedMonth” column to your Dates table, like so:

image
image1119×729 72.4 KB

With this in place, I added a copy of your YTD TY measure (needed because of the measure branching)

YTD TY V2 = 
IF( VALUES( Dates[CompletedMonth] ) = TRUE(),
    [YTD TY],
    BLANK()
)

With this result.

image
image943×573 37.2 KB

Here’s your file. I hope this was helpful.
YTD Test.pbix (156.4 KB)

2 Likes
4

This is an elegant solution. Nice and clean.:slight_smile:

1 Like
5

@DaveC, @uriah1977,

Here’s another approach:

 YTD TY Display = 

VAR CurrentMonthNum =
MONTH( TODAY() )

RETURN
IF( 
    SELECTEDVALUE( Dates[MonthOfYear] ) > CurrentMonthNum,
    BLANK(),
    [YTD TY]
)

Now use this in your visual instead of YTD TY (but rename it in the values box of the visual, so that the legend reads properly:

image
image731×442 25.7 KB

Hope this is helpful.

2 Likes
6

This forum is a treasure chest :smile:

7

@Melissa and @BrianJ,

Thanks for the great solutions! Right before seeing your replies, I came up with a working solution that is a similar line of thinking although less elegantly written:

YTD TY v2 = 
IF(MAX(Dates[Date]) <= EOMONTH(TODAY(), -1), 
    CALCULATE( [Total Amount],
        DATESBETWEEN(Dates[Date],
            DATE(YEAR(MAX(Dates[Date])), 1, 1),
            MAX(Dates[Date])
        )
    ),
    BLANK()
)

Glad to see that I at least had the right thought process! Thanks again for the quick replies!

2 Likes
8

@uriah1977,

This forum is a treasure chest :smile:

Could not agree more. Four different people with exactly the same tools, four completely different solutions. That’s why I find this endlessly fascinating…

  • Brian

P.S. And a fifth one for good measure:

1 Like