Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.
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]
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
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...