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
devika
Helper II
Helper II

Dynamically display last x quarters - line graph

Hi all,

I need to display the last 19 quarters from the current quarter dynamically.

 

The current quarter is 20231; the line graph should start showing from 20182 to 20231 and

When the new quarter starts in 20232, the line graph should show from 20183 to 20232 and so on.

The dataset is connected directly to the Oracle SQL server, storing 130000 rows.

Currently, in the measure, future quarters are truncated.

CHECK=

VAR MinDate =MinX(allselected(Merge[CASE_DATE]),Merge[CASE_DATE])

VAR MaxDate = MaxX(allselected(Merge[CASE_DATE]),MergeCASE_DATE])

RETURN CALCULATE(IF(MAX('Merge_Annual_Mx_Vss'[CASE_DATE]) < MinDate || MAX(Merge[CASE_DATE]) > MaxDate,BLANK(),value))

 

devika_0-1676451178680.png

I have looked into this thread, but it is slow to churn. Rolling QuarterEnd is not working.

https://community.powerbi.com/t5/Desktop/Dynamically-display-last-8-quarters/m-p/394240#M179827

 

Any help would be grateful.

1 ACCEPTED SOLUTION

Thank you for your time,

I made a slight tweak as it wasn't displaying the current quarter.

 

Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3*8)+1

var _maxDate = [MaxCaseDate]


return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@devika , In case there is no date filter

 

last 8 qtr

 

Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3*8)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

if you want select a qtr in slicer, then that slicer should come from an independent date table 

 

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -24) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Thank you for your time,

I made a slight tweak as it wasn't displaying the current quarter.

 

Last Qtr Today =
var _today = today()
var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
var _min = eomonth(_max,-3*8)+1

var _maxDate = [MaxCaseDate]


return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <=_maxDate)

))

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.