cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MBPCCX
Frequent Visitor

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

8 REPLIES 8
CNENFRNL
Super User
Super User

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 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 

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

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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!