Forum Discussion
vejas
Helper I
Helper I12 month rolling sales no future dates
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 ( ...
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])ReturnSales12*NoFutureDate
leed
Advocate II
Advocate IIvejas 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
vejasHelper I
Helper IMy 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.