Add Filter the Date (month) in X-axis

[Lemmon2314]

Hello Guys, i have a data from SQL Server and i want to x-axis (month) to be filtered 1year backward. See this screenshot

 so for january, the data i need is from feb 2017- january 2018, for February, data is from March 2016-February 2017.. and so on.. i have my SQL query on this.

SELECT [Call Priority],AVG(A.PriorityOccurence) january
 FROM xxxxxxxxx.dbo.Incidents a
 where  a.[Logged Date]>='2017-02-01' and a.[Logged Date]<='2018-01-31' -- January
--where  a.[Logged Date]>='2016-03-01' and a.[Logged Date]<='2018-01-31' -- February
--where  a.[Logged Date]>='2016-04-01' and a.[Logged Date]<='2017-02-31' --march
--where  a.[Logged Date]>='2016-05-01' and a.[Logged Date]<='2017-03-31' --april
--where  a.[Logged Date]>='2016-06-01' and a.[Logged Date]<='2017-04-31' --may
--where  a.[Logged Date]>='2016-07-01' and a.[Logged Date]<='2017-05-31' --june
-- and so on
 and YEAR(a.[Logged Date])<>'2015' and YEAR(a.[Logged Date])<>'2014'
 group by [Call Priority]
 order by [Call Priority]

[v-yuta-msft]

Hi Lemmon2314,

To achieve your requirement, please refer to DAX formula below:

Value in 1 year backward = CALCULATE(SUMX(Table1, Table1[Value]), DATESBETWEEN(Table1[Date], EOMONTH(TODAY(), 0) + 1, EOMONTH(TODAY(), 12)))

The result is like below and you can see PBIX file here:

https://www.dropbox.com/s/9ff0t4lvd63bi2m/For%20Lemmon2314.pbix?dl=0

Regards,

Jimmy Tao

[Lemmon2314]

HI @v-yuta-msft, thank you for your answer but it seems that it's not working. What i need if to fix the value in x-axis with 1 year backward data (already averaged) . its like asking how many apple i have in this particular date range (Feb2017-Jan2018: for january) compare to other date ranges like (Mar 2016 - Feb 2017:for February)

right now i just created 12 measures (1 for every month), to somehow get the data but visually wrong. In the picture below, i need

months (measures) to be in x-axis and the call priority to be the values. btw, i think creating 1 measure per month is somehow not advisable.

 

heres some of the month measures;

January = CALCULATE([Average Priority Occurence],FILTER(Incidents,Incidents[Logged Date].[Date]>=DAte(2017,2,1) && Incidents[Logged Date].[Date]<=DATE(2018,1,31)) )

February = CALCULATE([Average Priority Occurence],FILTER(Incidents,Incidents[Logged Date].[Date]>=DAte(2016,3,1) && Incidents[Logged Date].[Date]<=DATE(2017,2,28)) ) 

March = CALCULATE([Average Priority Occurence],FILTER(Incidents,Incidents[Logged Date].[Date]>=DAte(2016,4,1) && Incidents[Logged Date].[Date]<=DATE(2017,3,31)) )  

ETC...