Sameperiodlastyear function returning "Blank"

[mwadhwani]

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

[vik0810 · Accepted solution]

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 · Accepted solution]

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

[vik0810 · Accepted solution]

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

[mwadhwani]

@emarc1

Thanks a lot...!! It indeed helped me.

🙂

[emarc1]

@mwadhwani

Great!

[emarc1]

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.

[mwadhwani]

Hi @emarc1,

Thanks for the reply.

As of now I have data for the month July-2016, July-2017, Aug-2016, Aug-2017 only.

Please find the attached pbix file in the below link:

https://www.dropbox.com/s/n1021gcd72unjsf/POC.pbix?dl=0

Thanks