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
MBPCCX
Advocate I
Advocate I

Dynamic Current Quarter Filter

Hi all,

 

I have a scenario where I want to apply a dynamic page filter to include only sales data in the current quarter. For example, today being 14 October 2021, I only want to see sales in this quarter (October 2021 - December 2021). Throughout November and December the filter would remain the same (October 2021 - December 2021) and would change on the 1st of January 2022 where it would change to filter sales only for January 2022 - March 2022 and so on.

 

Model is fairly straight forward (Sales fact table and sales date dimension table). I was thinking of possibly adding a custom true/false column on the sales date dimension table to indicate whether a given date was in the current quarter but am a bit stuck on how.

 

Looking for any suggestions on how to approach this. Can provide more info if needed.

 

Thank you

1 ACCEPTED SOLUTION

@MBPCCX 

My apologies, I needed to shift the moths a bit.  Give this a try:

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _EndOfQtr = EOMONTH ( _StartOfQtr, 2 )
RETURN 
 [Date] >= _StartOfQtr && [Date] <= _EndOfQtr 

View solution in original post

15 REPLIES 15
jdbuchanan71
Super User
Super User

@AndrewKuharich 

We should just need to extend the number of months figuring into the end date like this.

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _End = EOMONTH ( _StartOfQtr, 11 )
RETURN 
 [Date] >= _StartOfQtr && [Date] <= _End
CNENFRNL
Community Champion
Community Champion

Simple enough, add a column of Year+Qtr in the date table this way,

YrQtr = Year( DATES[Date] ) * 10 + Quarter( Dates[Date] )

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

jdbuchanan71
Super User
Super User

@MBPCCX 

You can add a column to your Calendar table like this.

 

 

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ), 1 )
VAR _EndOfQtr = EOMONTH ( _StartOfQtr, 3 )
RETURN 
 [Date] >= _StartOfQtr && [Date] <= _EndOfQtr

 

 

2021-10-13_23-06-38.jpg

Then just set a filter on that column for True.  When the date rolls into next quarter the dates flagged True will shift to the new quarter.

Hi,

 

Thank you. This is close however, _StartOfQtr is returning the 1st of September as the start of the quarter resulting in four months instead of the quarter starting on the 1st of October.

@MBPCCX 

My apologies, I needed to shift the moths a bit.  Give this a try:

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _EndOfQtr = EOMONTH ( _StartOfQtr, 2 )
RETURN 
 [Date] >= _StartOfQtr && [Date] <= _EndOfQtr 

Hello - May I ask for a formula that would return "the current and next 3 Quarters", please?

Thank you!

 

Hey, is there a way to reuse this for last two quarters? Meaning current + last quarter?

@AlmantasPBIguy 

That would look somehting like this.

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _StartOfPQtr = EOMONTH ( _StartOfQtr, -4 ) +1
VAR _EndOfQtr = EOMONTH ( _StartOfQtr, 2 )
RETURN 
 [Date] >= _StartOfPQtr && [Date] <= _EndOfQtr
Anonymous
Not applicable

Hi ,
Even I have the same requirement. But as per my requirement ,I need to calculate " current quarter + Next quarter " sale. 
Can you please help me with the formula?
Thnaks in advance

@Anonymous 

For that we would just need this.

Current Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _EndOfNextQtr = EOMONTH ( _StartOfQtr, 5 )
RETURN 
 [Date] >= _StartOfQtr && [Date] <= _EndOfNextQtr

Hi, can I have the formula but for just previous quarter only please? I would really appreciate it.

@adude 

That would be something like this.

Last Qtr = 
VAR _StartOfQtr = DATE ( YEAR ( TODAY() ), FLOOR ( MONTH ( TODAY() ) -1 , 3 ) + 1 , 1 )
VAR _StartOfLastQtr = EOMONTH ( _StartOfQtr, -4 ) + 1
VAR _EndOfLastQtr = EOMONTH ( _StartOfLastQtr, 2 )
RETURN 
 [Date] >= _StartOfLastQtr && [Date] <= _EndOfLastQtr

Thank you!

Perfect thank you!

amitchandak
Super User
Super User

@MBPCCX , This Qtr vs last qtr based on today

 

This Qtr =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max),month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_min),month(_min)+3 ,1) -1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )


Last Qtr =
var _max = maxx(allselected('Date'), 'Date'[Date])
var _month = mod(month(_max),3)
var _min = date(year(_max)-1,month(_max) -1* if(_month=0,3,_month) ,1)
var _day = date(year(_min),month(_min)+3 ,1) -1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

 

 

based on selected date

 

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

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.