cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vejas Regular Visitor
Regular Visitor

12 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 ( LASTDATE ( 'Calendar'[Date] ) ) );
LASTDATE ( 'Calendar'[Date] ));'Calendar'[DatesWithSales] = TRUE()))
 
Problem is where fututre dates starts it shows all sales .
 
 
YtiYuuTj5P.png
 
What is wrong with this approach ?
 
Sinceraly 
1 ACCEPTED SOLUTION

Accepted Solutions
vejas Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

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

View solution in original post

6 REPLIES 6
leed Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

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 Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

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 Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

@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 Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

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.
Super User
Super User

Re: 12 month rolling sales no future dates

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
vejas Regular Visitor
Regular Visitor

Re: 12 month rolling sales no future dates

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

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 51 members 1,427 guests
Please welcome our newest community members: