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"))  Tot...
Need Help
Show and Tell
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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.