SAMEPERIODLASTYEAR not working as expected

[Anonymous]

My data is coming from an Access DB.

All these formulas worked fine I believed:

Total Sales = CALCULATE(SUM(raw[Value]),FILTER(raw,raw[Online/TE]="Online" && raw[Value/Volume]="Value")) 

Total Sales YTD = TOTALYTD(raw[Total Sales],raw[Date])

Total Sales YTD Last Year = CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR(raw[Date])) 

However - I tried to do drilldown and got this error relating to contiguos dates.  Therefore I added in a separate calendar file - generating data from the file I am using and linked them with a 1-Many relationship:

DimDate = ADDCOLUMNS(CALENDAR(

DATE(YEAR(MIN(raw[Date])), 1, 1),

DATE(YEAR(MAX(raw[Date])), 12, 1)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date],"Q" )

)

 This also seems to work fine. 

But now comes the problem - the formulas that were working for last year year-to-date now show the whole year instead of year-to-date?? 

 

Total Sales YTD Last Year = CALCULATE([Total Sales YTD], SAMEPERIODLASTYEAR(DimDate[Date].[Date]))

Any ideas why this is not working?  

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

Create a calendar table as below, change the "Date" column to "Date" format(originally, it is of "datetime“ format), then create a relationship between tables.

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "MonthNo", FORMAT ( [Date], "MM" ),
    "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonth", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthShort", FORMAT ( [Date], "mmm" ),
    "MonthLong", FORMAT ( [Date], "mmmm" ),
    "WeekNo", WEEKDAY ( [Date] ),
    "WeekDay", FORMAT ( [Date], "dddd" ),
    "WeekDayShort", FORMAT ( [Date], "dddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
        & FORMAT ( [Date], "Q" )
)

Create measures in your "raw" table

Total Sales =
CALCULATE (
    SUM ( raw[Value] ),
    FILTER ( raw, raw[Online/TE] = "Online" && raw[Value/Volume] = "Value" )
)

Total Sales YTD =
TOTALYTD ( raw[Total Sales], DimDate[Date] )

Total Sales YTD Last Year =
CALCULATE ( [Total Sales YTD], SAMEPERIODLASTYEAR ( DimDate[Date] ) )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.