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 adde...
Need Help
danextian's avatar
danextian
Super User
1 year ago

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'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