Getting Historical 6 month

[Jorgast]

Hello BI Community,

I am trying to get historical 6 month data so i can use it in a measure i am trying to build.

I am able to get the previous month, Difference, % Diff, but it is the previous 6m data that is throwing me off. In This example Oct-18 gets the sum,Average, % Diff from April-18 to Sept-18.

I have tried using the DATESINPERIOD function but that gives me the results including the current month. 

Below is a generic sample. This table is linked to a date table. 

Month	Current Month	Previous Month	Difference	% Diff	Sum Prev 6 M	average Prev 6 M	% Diff  past 6m
Mar-18	1,000,000
Apr-18	1,300,000	1,000,000	300,000	30%
May-18	1,400,000	1,300,000	100,000	8%
Jun-18	1,100,000	1,400,000	(300,000)	-21%
Jul-18	1,100,000	1,100,000	-	0%
Aug-18	1,000,000	1,100,000	(100,000)	-9%
Sep-18	950,000	1,000,000	(50,000)	-5%
Oct-18	1,000,000	950,000	50,000	5%	6,850,000	1,141,667	0.36%
Nov-18	800,000	1,000,000	(200,000)	-20%	6,550,000	1,091,667	-3.76%
Dec-18	800,000	800,000	-	0%	5,950,000	991,667	-8.38%
Jan-19	900,000	800,000	100,000	13%	5,650,000	941,667	-4.80%
Feb-19	800,000	900,000	(100,000)	-11%	5,450,000	908,333	-2.72%
Mar-19	900,000	800,000	100,000	13%	5,250,000	875,000	-3.06%
Apr-19	900,000	900,000	-	0%	5,200,000	866,667	-0.14%
May-19	1,000,000	900,000	100,000	11%	5,100,000	850,000	-1.02%
Jun-19	1,000,000	1,000,000	-	0%	5,300,000	883,333	4.17%
Jul-19	1,200,000	1,000,000	200,000	20%	5,500,000	916,667	4.17%
Aug-19	900,000	1,200,000	(300,000)	-25%	5,800,000	966,667	5.42%

[vanessafvg]

@Jorgast  how about using datediff?   eg date[date] =  dateadd(date, -6, m)

[Jorgast]

@vanessafvg 

Previous 6mth = CALCULATE([Current Month], DATEADD('TABLE'[DATE], -6,MONTH))

Gives me blank data

Previous 6mth = CALCULATE([Current Month], DATEADD('DATE TABLE'[DATE], -6,MONTH))

Gives me just the Current month from 6 months ago

[v-lili6-msft · Accepted solution]

hi, @Jorgast 

Just try this formula:

Previous 6mth = 

CALCULATE([Current Month], FILTER(ALL('DATE TABLE'),DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)>=1&&DATEDIFF('DATE TABLE'[Date],MAX('DATE TABLE'[Date]),MONTH)<=6))

Best Regards,

Lin