rolling 12 months formula dynamic

First you need a calendar table. Ready my article here https://exceleratorbi.com.au/power-pivot-calendar-tables/

make sure you have a MonthID column that co sisters of Integers counting unique months. 

Then write a custom time intelligence function. Read my article here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

Something like this

Total Sales Year MAT =
CALCULATE (
[Total Sales],
FILTER (
ALL ( Calendar ),
Calendar[MonthID] >= MAX ( Calendar[MonthID] ) - 11
&& Calendar[MonthID] <= MAX ( Calendar[MonthID] )
)
)

Hi ngadiez

You are doing Time-Intelligence calculations so you need a proper calendar table.

Once you have this Calendar Table, link it to your Main Table with the Dates Column and create the following measure:

SalesRolling = Calculate ( [Total Sales] , DatesInPeriod(Calendar[Dates], min(Calendar[Dates]) - 1, -1, YEAR)

where [Total Sales] = Sum(YourTable[SalesAmount])

In this example, DatesInPeriod starts from the minimum date in your current filter context, and shifts from 1 year using the Calendar table.

You can apply the same logic to the other measures.

Hi ngadiez,

Have you achieved your desired result? If yes, would you please kindly mark the helpful reply as an answer so that others having similar requirement can benefit from it?

Regards,
Yuliana Gu

I have a set of data:

Where I want to make Matrix like this:

For example, for Jan 17, Total sales is all transaction from 1 jan 2016 to 31 dec 2016. 
for every beginning of the month (BOM), I will calculate the number from the previous 12 months.

I know how to calculate each of the items if there is no matrix column, by using calculate and filter.

However, if there is matrix column, it will become more difficult. 

Is there any way to do this?

ngadiez,

My solution will not be elegant like Matt's but we have a different data culture at my company. Every time I provide data, I have to provide very specific filter context. That means I have to display the dates I use to make it very Fisher Price for consumers. I have a report similar to what you are building. I list our reps and their Moving Annual Net Invoiced total in a table. Beside that, I have a multi card that shows the dates used in the calculation. That reduces time spent responding to questions that I shouldn't have to answer because "Moving Annual Total" is a very specific title . . . but I digress. 

That means I use extra steps that may be be useful for you depending on  your company's data culture. First, as everyone else has said, make sure you have a good solid date table.

My next step is the basic calculations such as creating the Net Invoiced total. I will not walk you through that. I am sure you can handle the Sums.

Then I have a measure called Last day of previous month for simplicity and to make sure nobody misunderstands.

Last day of previous month: =

EOMONTH(TODAY(), -1)

Then a measure for the first day included in the calculation.

First date in period: =
EOMONTH ( TODAY (), -13 ) + 1

Finally, I have my moving annual net invoiced that only uses full months and ignores current month to date.

Annual Net Invoiced: =
CALCULATE (
    [Net Invoiced],
    DATESBETWEEN (
        DimDate[DateKey],
        [First date in period],
        [Last day of previous month]
    )
)

As I said, my company needs the extra information displayed. Yours may not. 

Anyway, hope this helps and who knows, MattAllington or Datatouille may find an error in my calculation I have not seen as I am no where near their level in this DAX thing.

Hi @ngadiez,

Please refer below stpes to get desired output
1. Clearly you will need one dim-table for data. You can use CalendarAuto function in New table to create that 

2. Create Custom Column for Month 

       Month  = EOMONTH(Table1[Transaction Date],-1)+1

3. Create a measure for Total Sales

       TotalSales = CALCULATE(SUM(Table1[Sales Amount]),DATESBETWEEN('calendar'[Date],PREVIOUSYEAR(FIRSTNONBLANK(Table1[month],TRUE())),FIRSTNONBLANK(Table1[month],TRUE())-1))

4. Now pull Month in column and TotalSales in values in Matrix

Hope this helps :)