12 month rolling sales no future dates

[vejas]

I am trying to get rolling 12 month sales, and i want to hide future dates. 

SALES12M = CALCULATE (

[Sales]; CALCULATETABLE (

DATESBETWEEN (

'Calendar'[Date];

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );

LASTDATE ( 'Calendar'[Date] ));'Calendar'[DatesWithSales] = TRUE()))

Problem is where fututre dates starts it shows all sales .

What is wrong with this approach ?

Sinceraly 

[vejas · Accepted solution]

Solution that works for me. Combine from 2 articles 

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

Rolling 12 months sales =

VAR Sales12 = CALCULATE (

[Sales];CALCULATETABLE(

DATESBETWEEN (

'Calendar'[Date];

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );

LASTDATE ( 'Calendar'[Date] )

);'Calendar'[DatesWithSales]=TRUE())

)

VAR NoFutureDate = MAX('Calendar'[DatesWithSalesNumber])

Return

Sales12*NoFutureDate

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[leed]

Hi @vejas 

SQLBI recently has published a blog about this kind of calculation: https://www.sqlbi.com/articles/previous-year-up-to-a-certain-date/

You can simply add a calculated column which contains 'Date'[IsPast] = TRUE in your formula and the result should show up correctly.

Best,

[vejas]

I dont want to compare periods i want shift back 12 moths every month and calculate sales. Just don't want sales be calculated for future months because they dont have full 12 moths yet. 

[leed]

@vejas  My bad explanation.

I mean you can use the principle describes in the blog, but not all.

So in your fact table, you create :

IsPast =

VAR LastSaleDate = MAX ( Sales[Order Date] )

VAR LastSaleDatePY = EDATE ( LastSaleDate, -12 )

RETURN

    'Date'[Date] <= LastSaleDatePY

and then in your measure, you just need to add:

'Date'[IsPast] = TRUE

[vejas]

My new aproach is to make a calculated column in calendar table 

DatesWithSalesNumber = IF('Calendar'[Date] <= MAX (Sales[OrderDate]);1), so if there are no sale no value in column.

I multiply formula result by DatesWithSalesNumber in did not get any number for future dates.