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
AbelardoRubilar
Regular Visitor

Display on a chart 12 months ago, starting with the selected month. Help

Hey, fellas.

I tell you that in the next report I want to show on the charts, 12 months, from the filtered month on the sheet, backwards.

AbelardoRubilar_0-1600196963130.png

You could make a slicer so that you can filter the charts, but the customer wants the chart to automatically show 12 months backwards when selecting the month above.

I found other solutions in this forum, but it should be noted that the filter above, also affects the calculations in the table below.

As a fact, I already have a date table.

Please stay tuned for some guidance.

Best regards

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @AbelardoRubilar

Due to I don't know your data model, I build a sample to have a test.

Table(Add Year column and Month column by calculated column):

 

Year = YEAR('Table'[Date])
Month = MONTH('Table'[Date])

 

1.png

Date Table:

 

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

 

I build two measures, one show piror 12 month values and one show future 12 month values.

 

Piror 12 Month = 
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MaxDate = MAXX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Piror12Month = EOMONTH(_MaxDate,-12)+1
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_MaxDate&&'Table'[Date]>=_Piror12Month))

 

 

Future 12 Month = 
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MinDate = MINX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Future12Month = EOMONTH(_MinDate,11)
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_Future12Month&&'Table'[Date]>=_MinDate))

 

Result is as below.

Default will show blank.

2.png

Select Year = 2019, Month = 2 in Slicers.

3.png

You can download the pbix file from this link: Display on a chart 12 months ago, starting with the selected month. Help

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @AbelardoRubilar

Due to I don't know your data model, I build a sample to have a test.

Table(Add Year column and Month column by calculated column):

 

Year = YEAR('Table'[Date])
Month = MONTH('Table'[Date])

 

1.png

Date Table:

 

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

 

I build two measures, one show piror 12 month values and one show future 12 month values.

 

Piror 12 Month = 
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MaxDate = MAXX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Piror12Month = EOMONTH(_MaxDate,-12)+1
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_MaxDate&&'Table'[Date]>=_Piror12Month))

 

 

Future 12 Month = 
Var _Year = SELECTEDVALUE('Date'[Year])
Var _Month = SELECTEDVALUE('Date'[Month])
Var _MinDate = MINX(FILTER(ALL('Date'),'Date'[Year]=_Year&&'Date'[Month]=_Month),'Date'[Date])
Var _Future12Month = EOMONTH(_MinDate,11)
Return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=_Future12Month&&'Table'[Date]>=_MinDate))

 

Result is as below.

Default will show blank.

2.png

Select Year = 2019, Month = 2 in Slicers.

3.png

You can download the pbix file from this link: Display on a chart 12 months ago, starting with the selected month. Help

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Ashish_Mathur
Super User
Super User

Hi,

You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@AbelardoRubilar , Beast is that you use relative date slicer. 

Or you can use rolling measure

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

But they can rollup the data into one date. So need another independent date table to achieve -

https://www.youtube.com/watch?v=duMSovyosXE

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.