Solved

moving 12 month total - power pivot DAX

Posted on 2016-09-13
1
16 Views
Last Modified: 2016-09-24
I am trying to produce a table that will give me my moving 12 month total.

sales start from 01/01/2013 and end in 01/08/2016 - there are no sales in my model before or after these dates.

I want my table only to display a value where it is possible to have a full 12 months worth of aggregated data. I therefore want to exclude the data highlighted in red below from the table.

Can anyone amend the measure below  to do this?

Note: Ideally I want the table to update automatically so that the range of dates included automatically extends as time goes on. For example, at the end of september 2016 another 12 month total will be included.

CALCULATE([total sales],DATESINPERIOD(BasicCalendarEnglish[DateKey],LASTDATE(BasicCalendarEnglish[DateKey]),-12,MONTH))
moving 12 month total
Thanks
Mike
0
Comment
Question by:mikes6058
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
Please see details here as I believe they can help:

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now