cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DebbieE Senior Member
Senior Member

SAMEPERIODLASTYEAR DAX not working with a year filter

I wanted to create a KPI showing year to Date and Last year to date.

 

So I have Measures

YTD Complaints = TOTALYTD('fact Fact'[Complaints],'dim Date'[date].[Date])
LY YTD Complaints = CALCULATE([YTD Complaints],SAMEPERIODLASTYEAR('dim Date'[date].[Date]))
 
I have a slicer on year so i can select the year to look at. Without the year selected its fine (Ive put the data into a table simply to have a look at)
Last Year issue 1.JPG
As you can see the last year information is shown in the column
 
if I choose 2019 to look at i want to see 2019 with Last year against it
Last Year issue 2.JPG
See how the Last year value dissapears. I was expecting that creating this measure would allow you to see Last years metric even with a year slicer set so im a little confused. 
 
How can i use a year slicer and keep the Last Year value against this years value?
1 ACCEPTED SOLUTION

Accepted Solutions
DebbieE Senior Member
Senior Member

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

After that Ive figured out what the issue is.

sply Solution 1.JPGHere I have used Year which is an actual data column in my date table to slice the data with and its not working

sply Solution 2.JPGThis time I have used year from my date hierarchy and its worked

 

Which to me meas that if you are using these kind of time based measures, if you want to slice by date you HAVE to use the time period from the date hierarchy rather than what I have done and used the Year column created in the date dimension

 

View solution in original post

6 REPLIES 6

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

Hi @DebbieE 

 

try this

LY YTD Complaints = CALCULATE(sum('fact Fact'[Complaints]),SAMEPERIODLASTYEAR('dim Date'[date].[Date]))
 
regards
DebbieE Senior Member
Senior Member

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

Ive changed to this logic but the same issue still stands

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

Can you show the relationship between dimDate and another table? Crossfilter are both or unique?

cisenberg Frequent Visitor
Frequent Visitor

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

I'm actually struggling with the exact same issue. I think that I know what the problem is, but I don't know how to correct it.

When you select a year with your slicer, you're changing the filter context applied to the visual. SAMEPERIODLASTYEAR() will only "see" the table generated by the current filter context, which means it will only contain 2018 data. You can't calculate based on the previous year since there's no other year in the current filter context!

I'm using a date slicer instead of a year slicer, but I tried using something like this:

PY Sales = 
VAR CurrentDateMax = MAX([INVOICE_DATE])
VAR CurrentDateMin = MIN([INVOICE_DATE])
VAR EarliestDate = MIN(DateDim[Date])
VAR PYDateMax = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate,
                DATE(YEAR(CurrentDateMax)-1, MONTH(CurrentDateMax), DAY(CurrentDateMax))
)
VAR PYDateMin = IF(YEAR(CurrentDateMin) < YEAR(EarliestDate), EarliestDate,
                DATE(YEAR(CurrentDateMin)-1, MONTH(CurrentDateMin), DAY(CurrentDateMin))
)
RETURN
CALCULATE(
    SUM([Net Ship $]),
    FILTER(ALL([INVOICE_DATE]),
    [INVOICE_DATE] >= PYDateMin &&
    [INVOICE_DATE] <= PYDateMax
    )
)

The idea is that we can store the currently-selected max and min dates, subtract a year from them, and then use this range to filter your wide-open date table.

 

EDIT: I found a fix to my code. This works with my visual, including drilldown!

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

@DebbieE an example of the sameperiodlastyear:

 

https://www.youtube.com/watch?v=eJ03-7MssuY

 

 

DebbieE Senior Member
Senior Member

Re: SAMEPERIODLASTYEAR DAX not working with a year filter

After that Ive figured out what the issue is.

sply Solution 1.JPGHere I have used Year which is an actual data column in my date table to slice the data with and its not working

sply Solution 2.JPGThis time I have used year from my date hierarchy and its worked

 

Which to me meas that if you are using these kind of time based measures, if you want to slice by date you HAVE to use the time period from the date hierarchy rather than what I have done and used the Year column created in the date dimension

 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,599 guests
Please welcome our newest community members: