Forum Discussion

MBPCCX's avatar
MBPCCX
Advocate I
4 years ago
Solved

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...
Need Help
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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
amitchandak's avatar
amitchandak
Super User
4 years ago

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.