Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jblackshear
Advocate III
Advocate III

SAMEPERIODLASTYEAR with a year filter

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.

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

 

 

 

 

 

 

 

View solution in original post

9 REPLIES 9
elenton
Helper II
Helper II

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

blopez11
Resident Rockstar
Resident Rockstar

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

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.

 

 

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

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.

 

 

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.

 

 

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.

 

 

 

 

 

 

 

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

 

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

glad that you got it all sorted and i could help



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.