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
DebbieE
Community Champion
Community Champion

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

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
evandrocunico
Resolver III
Resolver III

Hi @DebbieE 

 

try this

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

Ive changed to this logic but the same issue still stands

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!

@DebbieE an example of the sameperiodlastyear:

 

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

 

 

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

 

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

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.