rolling 12 months formula dynamic

[ngadiez]

Hi Everyone,

I have a table which consists of the channel of sales, transaction date, sales amount, the cost of the goods sold, and the sales commission.

Channel	Transaction Date	Sales Amount	Cost of Goods	Sales Commission
A	07-Dec-16	950	374	150
B	18-Feb-16	806	206	281
C	28-Jan-17	907	348	251
A	04-Aug-16	880	158	258
B	24-Jul-16	408	153	85
C	31-Aug-16	972	228	204
A	25-Jul-16	476	69	132
C	12-Dec-16	216	115	37
B	01-Apr-16	946	248	281
A	20-Dec-16	744	153	300
C	17-Dec-16	778	181	207
A	07-Nov-16	467	121	164
A	22-Jan-17	960	465	247
B	05-Jan-17	253	141	58
A	08-Aug-16	975	157	132
A	24-Mar-17	909	100	349
A	07-Mar-16	864	258	196
C	26-Feb-16	772	450	135
B	21-Dec-16	768	359	108
A	20-Jun-16	827	332	210
C	04-Apr-16	449	265	100
A	30-Jan-16	285	33	107
B	15-Apr-17	765	417	178
A	21-Mar-17	565	81	255
C	12-Feb-17	720	203	284
A	15-Sep-16	941	400	147
B	12-Dec-16	764	307	161
A	26-Feb-16	639	338	70
C	14-Nov-16	136	23	14

Every beginning of the month, I will take the previous 12 months data and calculate the total sales, the percentage of goods sold from total sales, the percentage of commission from total sales.

So for January 2017:

Total Sales= Calculate(SUM([Sales Amount]),FILTER(Table1,AND([Transaction Date]<01/01/2017,[Transaction Date]>=01/01/2016)))

Of course this will do if I just want to calculate the total sales amount only for january.

However I dont know what should I do if I want to change the valuation month: 

example for Feb 2017: 

Total Sales= Calculate(SUM([Sales Amount]),FILTER(Table1,AND([Transaction Date]<01/02/2017,[Transaction Date]>=01/02/2016)))

How can I produce some table like this? where the formula is based on the valuation month:

BOM	Jan-17			Feb-17			Mar-17
Channel	Total Sales	% Cost of Good	%  Commission	Total Sales	% Cost of Good	%  Commission	Total Sales	% Cost of Good	%  Commission
A
B
C

Thank you so much for helping. 

Tagging the pro user: @MattAllington, @kcantor, @Datatouille, @MiguelMartinez, @elliotdixon

[sumit4732]

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 🙂 

[v-yulgu-msft]

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

[kcantor]

@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.

[Datatouille]

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.

[MattAllington]

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] )
)
)

[ngadiez]

I have a set of data:

Channel	Transaction Date	Sales Amount	Cost of Goods	Sales Commission
A	07-Dec-16	950	374	150
B	18-Feb-16	806	206	281
C	28-Jan-17	907	348	251
A	04-Aug-16	880	158	258
B	24-Jul-16	408	153	85
C	31-Aug-16	972	228	204
A	25-Jul-16	476	69	132
C	12-Dec-16	216	115	37
B	01-Apr-16	946	248	281
A	20-Dec-16	744	153	300
C	17-Dec-16	778	181	207
A	07-Nov-16	467	121	164
A	22-Jan-17	960	465	247
B	05-Jan-17	253	141	58
A	08-Aug-16	975	157	132
A	24-Mar-17	909	100	349
A	07-Mar-16	864	258	196
C	26-Feb-16	772	450	135
B	21-Dec-16	768	359	108
A	20-Jun-16	827	332	210
C	04-Apr-16	449	265	100
A	30-Jan-16	285	33	107
B	15-Apr-17	765	417	178
A	21-Mar-17	565	81	255
C	12-Feb-17	720	203	284
A	15-Sep-16	941	400	147
B	12-Dec-16	764	307	161
A	26-Feb-16	639	338	70
C	14-Nov-16	136	23	14
A	20-Apr-17	256	105	71
A	23-Aug-16	770	244	307
B	22-Apr-16	499	63	60
B	12-Jun-16	864	422	139
A	16-Jun-16	179	107	20
C	03-Jul-16	381	181	55
A	21-Oct-16	557	201	150
B	05-Mar-17	529	71	132
A	01-Mar-17	880	476	224
C	23-Jun-16	448	131	65
A	16-Nov-16	192	115	25
B	07-Jun-16	136	30	21
A	17-Jan-16	854	109	417

Where I want to make Matrix like this:

BOM	Jan-17			Feb-17			Mar-17			Apr-17
Channel	Total Sales	% Cost of Good	%  Commission	Total Sales	% Cost of Good	%  Commission	Total Sales	% Cost of Good	%  Commission	Total Sales	% Cost of Good	%  Commission
A	10600	29.90%	26.27%	10421	33.51%	24.07%	9782	32.24%	24.92%	11272	31.52%	27.24%
B	5191	34.44%	21.88%	5444	35.43%	21.93%	4638	37.15%	19.69%	5167	34.72%	20.22%
C	4152	37.91%	19.68%	5059	37.99%	21.11%	5007	33.45%	24.31%	5007	33.45%	24.31%

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?