Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SAMEPERIODLASTYEAR not working as expected

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?  
 
Need Help
Show and Tell

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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