YTD Chart Not Working Properly

[WishAskedSooner]

I am playing around in the Adventure Works PBIX. One scenario I am testing is when the dates don't align exactly in the fact table. For example, I edited the fact table and deleted all the rows for Bikes sales in June 2020 (the latest month of sales). This means that Bikes don't have any June 2020 sales loaded as of yet while the other Categories do.

Now, I want to create a YTD chart that plots against the prior year. I can easily create the following measures:

Sales_YTD_1 = CALCULATE(SUM('Sales'[Sales Amount]), DATESYTD('Date'[Date]))

Sales_PY_YTD_1 = CALCULATE(SUM('Sales'[Sales Amount]), SAMEPERIODLASTYEAR(DATESYTD('Date'[Date])))

The next thing I do is create an unconnected copy of the Date table and a new column simply Year = YEAR('Date'[Date]). Then, I created the following plot using the above two measures in the Y-axis and Month from the unconnected Date table for the X-axis with a filter applied to the chart on Year = 2020. I also have a slicer on the page for Category.

Now, there are a couple of things I don't like about this plot.

1. I don't like the way the Sales_YTD_1 flatlines for the rest of the year.

2. I don't like how I have to hard code the value 2020 in the filter on Year

So, I decided to create my own DATESYTD function, if you will. First, I created a measure that will return the MAX OrderDateKey from the fact table that is filter-context sensitive (important since the Bikes no longer have orders past May 2020). Next, I created a measure that returns the year of the MAX OrderDateKey:

LatestOrderDateKey = MAX('Sales'[OrderDateKey])

LatestOrderDateYear = MAXX(FILTER('Date', [DateKey] = [LatestOrderDateKey]), [Year])

And, they both work as expected.

Next, I updated my Sales_YTD_1 measure with the following:

Sales_YTD_2 =

    VAR YTD_Months = SELECTCOLUMNS(FILTER('Date', [Year] = [LatestOrderDateYear] && [DateKey] <= [LatestOrderDateKey]), [Date])

    RETURN

        CALCULATE(SUM('Sales'[Sales Amount]), YTD_Months)

It sort of works. No more flat lining. So, that is good. But, it is no longer cumulative and I don't understand why. In fact, I don't understand what it is about DATESYTD inside CALCULATE that even tells it to do a cumulative summation. I tried wrapping YTD_Months with DATESYTD() but that just breaks it.

Furthermore, PowerBI won't let me enter a measure as a filter value for my chart. So, I don't know how to make the Year filter dynamic and tied to the measure I created.

Maybe I am doing this all wrong. As always, any help is greatly appreciated!

[parry2k · Accepted solution]

@WishAskedSooner try something like this, replace table and column names as per your model

YTD Last Transaction Date = 
VAR __LastTransactionDate = CALCULATE ( MAX ( Sales[OrderDate] ), ALL () )
RETURN
CALCULATE (
    [Sales],
    DATESYTD ( 
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            KEEPFILTERS (
                'Calendar'[Date] <= __LastTransactionDate
            )
        )
    )
)

[WishAskedSooner]

@parry2k , thank you so very much for your thoughtful reply. I am marking it as a solution. I had to make some edits to get it working properly in my model. Here is the final measure that I came up with using your example:

Sales_YTD_2 =

    VAR _LatestOrderDateKey = MAX('Sales'[OrderDateKey])

    RETURN

        CALCULATE(SUM('Sales'[Sales Amount]), DATESYTD(CALCULATETABLE(VALUES('Date'[Date]), 'Date'[DateKey] <= _LatestOrderDateKey)))

Some things I don't quite understand.

- I could not use the LastOrderDateKey measure that I created in the DAX above. I had to define a VAR inside the measure. I find this behavior odd.

- Furthermore, if I forgoe creating a VAR and just stick the MAX('Sales'[OrderDateKey]) in the CALCULATETABLE function, it loses the filter-context even if I wrap it with KEEPFILTERS like your example.

Don't get me wrong. I am super happy that it works if I define the VAR and use like above. But, there are some aspects of DAX that I simply do not understand.

As for the dynamic filter on Year, I will probably make a separate post on that.

Thanks!