Find PREVIOUSQUARTER from TODAY

[WARIDO]

Hi

I am trying to get the PREVIOUSQUARTER from TODAY. For example the previous quarter from today would be Qtr 2 (April, May, June).

I have created a new column within table ContactActions

Today = TODAY()

And another column, again within ContactActions:

PreviousQuarter = PREVIOUSQUARTER(ContactActions[Today])

This is returning all dates (year, quarter, month and day).

Your help would be really appreciated.

Thank you,

Wayne

[v-yadongf-msft]

Hi @WARIDO ,

Is this the result you want?

Please try following DAX:

Today = TODAY()

PREVIOUSQUARTER = "Q" & INT(FORMAT('Sheet4'[Today],"q")-1)

By the way, please note: PREVIOUSQUARTER returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context.

Best regards,

Yadong Fang

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

[PijushRoy]

@WARIDO 

Can you please share demo pbix file with similar data model and table structure.
Fiscal Year and calender year should same format.

Thanks

[WARIDO]

Hi @PijushRoy I will put this together tomorrow and share. What is the best way of sharing the pbix file?

[PijushRoy]

Google Drive/SharePoint etc but need public access

[WARIDO]

@amitchandak when I paste this in it is not understanding the [net] element of the formula. My date table does not have a [net]. Could you help?

[WARIDO]

@amitchandak I have tried several different attempts using my available fields and no luck. Are you able to assist any further? Thanks 🙂

[WARIDO]

@amitchandak thank you very much. I will save these!

[amitchandak]

@WARIDO , All Qtr formula based on today

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

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

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

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

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

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)+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

in case there is filter on date table, the use all. Example

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)+1
return
CALCULATE([Net], FILTER(all('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max))

https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

[PijushRoy]

Hi @amitchandak 

Very usefull, Thanks for sharing

[WARIDO]

Hi @PijushRoy are you able to help with the 'net' element of the formula? I am unable to get this working.