Differences with SAMEPERIODLASTYEAR and Dates

[calliandutra]

Hi,

I have a doubt with DAX formula SAMEPERIOLASTYEAR.

To explain, I will use a real example. (I'm a Brazilian, so my screenshots are in Portuguese, but it's easy to understand)

I have a dimension table "Calendar" and a fact table "Sales". The sales date is linked with date Calendar.

There's two formulas

$ Sales = SUM(fCtrc[VL_TOTAL])
$ Sales Last Year = CALCULATE( SUM(fCtrc[VL_TOTAL]); SAMEPERIODLASTYEAR( dCalendar[Date] ) )

Here the things are perfect. All DAX are working correctly.

My problem is:

In my example, "today" is 2017-03-03.

In Last Year the sales during all March (2016) was $8.079,656,38.
Between 2016-03-01 and 2016-03-03 (three days) was $ 888.203,72

When I put the fields in a table (Year; Sales; Sales Last Year), instead March 2017 consider just March 2016 until 2016-03-03, the formula consider all March 2016 month.

But, if I put a filter considering until Yesterday (2017-03-02), the formula consider correctly.

I was clear? If it's confuse I can try explain better.

Thanks

.WrongCorrectly

[Anonymous]

Hi @calliandutra,

Do you create measures in your sceanrio? If that is the case, how about  creating a calculated column using the following formula?

sales last year = CALCULATE([$ Sales]; SAMEPERIODLASTYEAR(dCalendar[Date]))

When I use data slicer to filter sales last year column,everything works as expected.

Thanks,
Lydia Zhang