Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Line chart showing 5 year evolution of Moving Annual Total

Hi!

 

I need to make a report that has a Line Chart object to show the a 5 year evolution of Moving Annual Total.

 

My goal is: Have a time selector in the report for selecting the month-year (example jun-2017) of last Moving Annual Total to visualize. In this case, if the selection is jun-2017, the Chart must show the evolution of 5 Moving Annual Total, from jun-2013 to jun-2017.

 

How can I perform this?

 

Thanks in advance.

3 REPLIES 3
v-caliao-msft
Employee
Employee

@Anonymous,

 

Please refer to the steps below to see the details.

  1. Create a date table.
    Table = ADDCOLUMNS(FILTER(CALENDAR(DATE(2010,1,1),DATE(2017,6,30)),DAY([Date])=1),"YearMonth",FORMAT([Date],"YYYYMM"))
    Capture.PNG
  2. Create a measure in original table.
    Measure =
    var selecteditem = MAX('Table'[Date])
    return CEILING(1*(selecteditem-MAX(Sales[Date]))/30,1)
  3. Add measure to chart filter
    Capture1.PNG

Result
Capture2.PNG

 

Regards,

Charlie Liao

Anonymous
Not applicable

Hi @v-caliao-msft.

 

Many thanks for your reply and help so far.

 

I'm not sure what happened, but 2 different posts got merged, like a single requirement. Actually, they are similar, but different.

 

1 - One requirement will be use a Line Chart to display the evolution of Moving Annual Total for fast past 5 yearas, but of the period selected in the timeline. So, in this case, if I select 201706, what I would get in the chart is for every month between 201706 and 201206, the Moving Annual Total. This part is important, it has to be the Moving Annual Total, not that only month sales.

 

2 - The second requirement will be use a Bar Chart to display the 5 last Moving Annual Total of selected period. In this case, if I select 201706, what I would get are 5 bars with the Moving Annual Total for 201706, 201606, 201506, 201406, 201306.

 

Hope this helps to clarify.

 

Thanks in advance.

@Anonymous,

 

I split this thread into two threads. And for the Moving Annual Total, please create a measure using the DAX below
Moving Annual Total = CALCULATE(SUM(Sales[Sales]),FILTER(ALL(Sales),Sales[Date]<=MAX(Sales[Date])&&Sales[Measure]>=-1&&Sales[Measure]<=36))

Use this measure in your visual.
Capture.PNG

 

Regards,

Charlie Liao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.