SAMEPERIODLASTYEAR with a year filter

[jblackshear]

I am trying to display sales data for the current year, with a measure for the current month's sales, YTD sales together with last year's YTD. We only want to display a single year's data on this report/dashboard, although I have loaded three years worth of data into the model to give previous YTD for last year as well, if needed. (This is a demo/prototype that will eventually be a real dashboard, to demonstrate to the execs some additional kinds of things they can see and do.) There will be a slicer for year (although they will almost always only look at the current year), and slicers for a few other values as well, such as Division, and down to individual Customer.

I am new to Power BI and clearly have not yet mastered the intricacies of evaluation context. I have created three different versions of my RevenueLastYTD measure (actually many many more but these are the three that mostly work but with a significant problem). Each measure returns the correct values when it returns anything at all. So that's something.

RevenueLastYTD = CALCULATE([RevenueYTD], SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))

The simplest and most straight-forward. It works just fine as long as I have no date filter. But as I said, I need to display only a single year. When I have the date filtered to 2016, the 2015 data is invisible to the measure and it returns blanks for the previous YTD measure.

The next measure was an attempt to overcome that problem, by getting All(Dates) before calculating SAMEPERIODLASTYEAR.

RevenueLastYTDv2 = CALCULATE([RevenueYTD], ALL(Dates), SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))

Now last year's data is available to my measure even when I filter the visual to 2016. The problem is that it also adds rows for every other date I have available, with blanks for every field except RevenueLastYTD.  This is not what I want, either.

I understand that CALCULATE does some things with context, but I do not yet fully understand how to work with it. So I tried a different measure without CALCULATE.

RevenueLastYTDv3 = TOTALYTD(SUM(SalesDataAggregated[TotalRevenueThisPeriod]), SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))

This has exactly the same results as the first version; it works fine with no year filter but displays no data (because it can't see it) for previous YTD when there is a year filter active.

Can someone help me get this measure right, and at the same time help me better understand Power BI and DAX? I'm thinking that the second version above might be on the right track, but with some additional refinement.

 Edit: I am looking into the KEEPFILTERS function combined with ALLEXCEPT as a way to keep all the filters that have been applied except the Year filter. But I haven't quite got it right yet. If anyone has any guidance to offer here - or if that is not a useful direction for this problem, I would love to hear it.

[elenton]

Hi @jblackshear  did you get a fix for this in the end as i have exactly the same issue currently?

[blopez11]

The following has worked for me in the past, assuming your date table is Dates, where CalcDate is of type Date:

RevenueYTD = CALCULATE(SUM (...))

RevenueLastYTD = CALCULATE([RevenueYTD], SAMEPERIODLASTYEAR(Dates[CalcDate]))

Then for like a clustered bar chart, have RevenueYTD and RevenueLastYTD in the Values area and Month from your date table in the Axis area, then as you mention use Year from your date table as a slicer

[jblackshear]

I appreciate your response, thank you.

I tried out the measures as you describe, which are much like my first attempt shown above. 

RevenueYTDv2 = CALCULATE([TotalRevenue], DATESYTD(Dates[CalcDate].[Date]))

RevenueLastYTDv5 = CALCULATE([RevenueYTDv2], SAMEPERIODLASTYEAR(Dates[CalcDate].[Date]))

I get the same results as with my previous attempt - when I add the year slicer, the previous YTD values disappear.

[blopez11]

Have you tried removing the .[DATE] from Dates[CalcDate].[Date]?

[jblackshear]

I do not yet understand what the date column does with and without the [date], [year], etc. bits added on.

But when I leave off [date], instead of getting year-to-date total I get only the total for that month.  

I end up trying everything with both variations to see if one works and the other doesn't. I then use the least specific one that works. Not very precise or technical, I know. I hope some good understanding comes soon because I don't like working this way.

[richbenmintz]

if you a introducing a filter then you will need to clear the date context in your calcution and then filter for the previous period.

an example would be something like,

calc=calculate(expression, filter(all(dates), date[year] = max(date[year])-1), that is if you have a date dimension with a year column.

If you are trying to get the same month and you have a monthcolum like 201611, you could use the same pattern like calc=calculate(expression, filter(all(dates), date[year] = max(date[monthcolumn])-100). 

This Pattern can be used for all prior period calc.

Hope that helps.

[jblackshear · Accepted solution]

I wish I could accept both answers as the solution, because I appreciate your help in pointing me in the right direction.

I had tried many different variations on your suggestions, with close-but-not-quite-right results.

I thought that maybe the date intelligence functions were not working quite right with my date table, and decided to rebuild it. The dates in my data warehouse are integers of the form 20161121. Originally, I brought them in as is, then created a calculated column in Power BI to create the date field using the date parts (year, month, day stored in separate fields). Then I matched the tables up in the Power BI designer.

This time I created the calculated date key in the SQL query, for both tables. Now when I use the normal date functions, they work as expected. It definitely helped to know the syntax that is supposed to work, so again I thank you both.

What I ended up with for my RevenueLastYTD measure is:

RevenueLastYTD = CALCULATE([RevenueYTD], FILTER(ALL(Dates), Dates[CalendarYear]=MAX(Dates[CalendarYear])-1), SAMEPERIODLASTYEAR(Dates[Date]))

It correctly displays, for each month, the YTD for the same month of the previous year.

[jasonahood]

I couldn't get this work, but doing it this way resolved the issue.

CALCULATE([Measure], FILTER(ALL(Dates), Dates[Year] = SAMEPERIODLASTYEAR(Dates[Date])))

[richbenmintz]

glad that you got it all sorted and i could help