Forum Discussion

verschug's avatar
verschug
New Member
1 year ago

Excel Power Pivot - issue Function SAMPLEPERIODLASTYEAR only works with contiguous date selections

Hi all,

I have an error: Function SAMPLEPERIODLASTYEAR only works with contiguous date selections.

 

I have a sales table with monthly sales data for 3 years. 

I have added a date table. And added the relationship.

I created measures:

sqm:=sum([Surface])

sqm_prev_year:=CALCULATE([sqm];SAMEPERIODLASTYEAR('Calendar'[Date]))

 

Both measures are working fine when I use Year as row.

Or Year as column and YYYY-MM as row. 

But I need year as columns and month number as row. So I would have only 12 rows for the 12 months. Adding the month number to the pivot table results in following error. Calculation error in measure Function SAMPLEPERIODLASTYEAR only works with contiguous date selections.

What is the problem? 

Kr, Guy Verschueren

 

 

 

 

 

 

Need Help

4 Replies

    • verschug's avatar
      verschug
      New Member
      1 year ago

      Hi danextian,

       

      This solution works fine for the measure "sql_last_year".  Thanks. 

       

      But I still have another similar problem in my report.

      I have a measure to calcualate the YTD value

      => sqm_YTD:=CALCULATE([sqm];DATESYTD('Calendar'[Date]))

      And also a measure to calucalate the prev year YTD value

      => calculate sqm_YTD_PrevY:=CALCULATE([sqm_YTD];SAMEPERIODLASTYEAR('Calendar'[Date]))

      And this last one also gives the same error concerning the SAMEPERIODLASTYEAR. 

      * Do you also know an alternative formula? 

      * Do you know why these measures are not working anymore, while they did work fine in the past?

       

      Kind regards, Guy 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi verschug ,

     

    danextian's workaround is great.

    Could you please tell us if you have solved your problem?

    If so, it's good to accept the reply as solution. More people will benefit from it because of this.

    If not, please feel free to tell us.

     

    Best Regards,
    Stephen Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • verschug's avatar
    verschug
    New Member
    1 year ago

    Hi danextian,

    * I also just noticed that the Grand Total for the measure sqm_prev_year does not summarize but is the data of the last mont. Can this be solved? 

    Kind regards, Guy