Is this possible? DATEINPERIOD not working the way I expected

[peterhui50]

Hello,

I have a dataset here that is linked to a date table.

What I want to do is to do a rolling 12 month addition. 

So I created a measure

```

Sales_12_Months =

CALCULATE (

[Sales],

DATESINPERIOD ( DATE_TABLE[Date], LASTDATE ( DATE_TABLE[Date] ), -12, MONTH )

) ```

but when I added in the Year, this measure started to work differently than expected. It's likely it is because the year is added, so it filters differently.

Is this possible? 

Thanks!

[littlemojopuppy]

Hi @peterhui50 is that first snip your actual dataset?  Because it looks like an Excel pivot table (which summarizes data).  And your rolling 12 month looks correct.

[peterhui50]

Looks like this worked

Sales_12_Months =

CALCULATE([Sales],DATESINPERIOD(DATE_TABLE[Date],ENDOFYEAR(DATE_TABLE[Date],"2/28"),-12,MONTH))

[peterhui50 · Accepted solution]

I just did a SWITCH statement to include an additional column to specify what is "CURRENT PERIOD" vs "PREVIOUS PERIOD" and just did visual level filter. It's a bit easier.