Why SAMEPERIODLASTYEAR function does not generate correct result

Question

First I calculated Year to date written Premiu:

YTD WP = TOTALYTD([Ttl WP],dim_Date[Date])  //works fine

Then in order to calculate the same for previous year I'm using SAMEPERIODLASTYEAR function:

PY WP = CALCULATE([YTD WP],
                SAMEPERIODLASTYEAR(dim_Date[Date]), ALL(dim_Date)
)

The result should be $72,550 M. Where does it get $127.60M???

So, unfortunately it does not give me correct result.

I have similar report already and function works fine, but why it doesn't work in this situation?

UPDATE 1:

Using DATESYTD to get numbers for previous year also gives me 127M

PY WP = CALCULATE([YTD WP],
                DATEADD(DATESYTD(dim_Date[Date]),-1,YEAR)
)

UPDATE 2:

PY WP = CALCULATE(
                [YTD WP], SAMEPERIODLASTYEAR(DATESYTD(dim_Date[Date])), ALL(dim_Date) 
                )

UPDATE 3:

Marked dim_Date table as "Date" table.

PY WP = TOTALYTD([Ttl WP], SAMEPERIODLASTYEAR(dim_Date[Date]))

But still gives incorrect number.

I used filter function to make sure what is the number should be:

PrevYear = CALCULATE([Ttl WP],FILTER(dim_Date,dim_Date[Date] >=VALUE("2018-01-01") && dim_Date[Date] <=VALUE("2018-03-31")))

And I got correct number.

This is a bit of a tangle of interacting multiple date filters. Does it work if you wrap dim_Date[Date] with DATESYTD in your PY WP measure? — Alexis Olson
Just tried this and still gives me the same incorrect number PY WP = CALCULATE([YTD WP], DATEADD(DATESYTD(dim_Date[Date]),-1,YEAR) ) — Serdia
I meant PY WP = CALCULATE([YTD WP], SAMEPERIODLASTYEAR(DATESYTD(dim_Date[Date])), ALL(dim_Date) ), but I don't remember if you can even pass in an expression to that function. — Alexis Olson
Yes. Thank you . I was able to run it. But still the same result. 127 M — Serdia
Why are you passing ALL(dim_Date) to CALCULATE? Do you mean ALL(fact_premium)? — World Wide DBA

World Wide DBA World Wide DBA · Accepted Answer · 2019-04-17T22:59:24

You forgot to mark the dim_Date table as the Date Table. Without this, the Time Intelligence functions do not work correctly.

To do this, right click the dim_Date table, and choose Mark as Date Table from the context menu. You can then choose the Date column as the key. The following snippet is taken from Time Intelligence in Power Pivot documentation but it applies to all products compatible with DAX.

In order to work with time-intelligence functions, you need to have a date table included in your Data Model. The date table must include a column with one row for every day of each year included in your data. This column is considered to be the Date column (although it can be named whatever you like). Many time-intelligence functions require the date column in order to calculate according to the dates you select as fields in a report. For example, if you have a measure that calculates a closing quarter-end balance by using the CLOSINGBALANCEQTR function, in order for Power Pivot to know when the end of the quarter really is, it must reference the date column in the date table to know when the quarter starts and ends.

Once this is done, your calculations work fine - though your PY WP measure can be simplified to:

[PY WP] = TOTALYTD([Ttl WP], SAMEPERIODLASTYEAR(dim_Date[Date]))

For more information, see Set and use date tables in Power BI;

Why SAMEPERIODLASTYEAR function does not generate correct result

1 Answers