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

[verschug]

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

[verschug]

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

[Anonymous]

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

[danextian]

Hi @verschug 

Try this formula:

=CALCULATE (
    [Total Revenue],
    FILTER (
        ALL ( DatesTable ),
        DatesTable[Year]
            = MAX ( DatesTable[Year] ) - 1
            && DatesTable[Month Short] = MAX ( DatesTable[Month Short] )
    )
)

Please see the attached excel file.

[verschug]

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