Forum Discussion

mwadhwani's avatar
mwadhwani
Kudo Kingpin
8 years ago
Solved

Sameperiodlastyear function returning "Blank"

Hi Experts,

 

Fact Table: f_WS(dateid,SalesValue)
Dimension Date: d_date(dateid,date,quarter,month,year)

 

I want the SalesValue this year and last year in separate measures.
Following is the formula, I am using:

ThisYearSales=sum(SalesValue)

LastYearSales=Caluclate(sum(SalesValue),Sameperiodlastyear(d_date.date))

Following slicers are used:

Year slicer pulled from date dimension.

 

Issue:

Year has values-2014,2015,2016,2017(All years have sales value in the fact table )

Suppose I select 2016 ThisYearSales comes perfect  but LastYearSales comes (Blank) even though there is data for all the years.

 

Also, I pulled date from d_date in a slicer and tried to calculate ThisDaySales and LastDaySales using following formula:

ThisDaySales=sum(SalesValue)

LastDaySales=Caluclate(sum(SalesValue),PREVIOUSDAY(d_date.date))

I am getting PERFECT OUTPUT at Day level.

Granularity of fact table is at Day level.

 

Any help or suggestion woulb be appreciated.

 

Thanks

Need Help
  • vik0810's avatar
    vik0810
    8 years ago

    Its because in PBI Desktop you can not mark a table as date table. So the year slicer is not reset by sameperiodlastyear. The simplest was would be to adjust your measures.

     

    Previous Year Sales = CALCULATE(Measure, SAMEPERIODLASTYEAR(Dates[date]), ALL(Dates))
  • vik0810's avatar
    vik0810
    8 years ago

    I downloaded your file, adjusted the meuasure and it works now

6 Replies

  • vik0810's avatar
    vik0810
    Resolver V
    8 years ago

    Its because in PBI Desktop you can not mark a table as date table. So the year slicer is not reset by sameperiodlastyear. The simplest was would be to adjust your measures.

     

    Previous Year Sales = CALCULATE(Measure, SAMEPERIODLASTYEAR(Dates[date]), ALL(Dates))
  • emarc1's avatar
    emarc1
    Advocate II
    8 years ago

    Your measures seem about right. You should probably refer to 'd_date'[date] rather than d_date.date. You've spelt Calculate wrong but I assume that's just because you've typed it here.

     

    CALCULATE(
SUM( SalesValue ),
SAMEPERIODLASTYEAR( 'd.date'[Date] )
)

    Perhaps you're filtering in a way that's hiding the previous year data. I don't think that would usually cause problems though.

     

    Take screenshots of your date table and the report page or upload a pbix somewhere.

  • vik0810's avatar
    vik0810
    Resolver V
    8 years ago

    I downloaded your file, adjusted the meuasure and it works now