Forum Discussion

LRutherford's avatar
LRutherford
Regular Visitor
8 years ago

sameperiodlastyear

Hi folks,

 

I'm trying to get SAMEPERIODLASTYEAR to work.  My measure is:

 

JOBS LAST YEAR = calculate([JOBS], sameperiodlastyear(DATETABLE2[Date]))

where JOBS is JOBS = sum(data1[job number])

 

I have tried various date tables including:

 

1. Creating a date table (DATATABLE2) within Desktop using DAX Dates = CALENDAR(TODAY()-800,TODAY()+800)

2.  I link this to my data1 table in a one to many relationship (I have many duplicate dates in the data1 table)


 

I get null results from Jobs Last Year measure when using the DATATABLE2 in the formula. 

 

I've also tried creating a different date table from here:

 

https://databear.com/power-bi-tip-dynamic-calendar-table/

 

which creates:

 

I get null results from Jobs Last Year measure when using this second date table in the formula.

 

 I've checked the relationships, and the data type of the dates in the 3 different tables and all are set to date.  

 

As the date filter I've set-up a slicer using both the Date field in the Date Table (tried both date tables) and the date field in the Data1 table.  Both give a Null result. 

 

Can anyone point out what I'm doing wrong. 

thanks in advance.

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi LRutherford，

     

     

    From your description, it seems that the syntax of your measure is right. If I understand your scenario correctly, by my test, the measure worked in my environment. You could refer to the pictures below.

     

    Picture 1-the relationship of two tables

     

     

    Picture 2-the result of the measure

     

     

    If that is not the case, could you share your dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

     

    Best regards,

    Cherry