Forum Discussion

paulfink's avatar
paulfink
Post Patron
4 years ago
Solved

Custom Quarter - Quarter Button Slicer

Hi guys,

 

Need a hand in making a formula for a Column that returns either Current Quarter or Previous Quarter so i can use those to put into a slicer to filter my pages.

 

This is what i have so far:

FiscalQrt = SWITCH('Calendar Table'[Month Number], 1, "Q3", 2, "Q3", 3, "Q3", 4, "Q4", 5, "Q4", 6, "Q4", 7, "Q1", 8, "Q1", 9, "Q1", 10, "Q2", 11, "Q2", 12, "Q2")

 

FY = IF('Calendar Table'[Date].[MonthNo]>6, 'Calendar Table'[Year] & " / " & 'Calendar Table'[Year]+1, 'Calendar Table'[Date].[Year]-1 & " / " & 'Calendar Table'[Date].[Year])

 

YTD QTR = LEFT('Calendar Table'[FY], 12) & " " & RIGHT('Calendar Table'[FiscalQrt], 2)

 

Our FY is from July to June next year e.g July 21 - June 22

 

Q1 = Jul, Aug, Sept

Q2 = Oct, Nov, Dec

Q3 = Jan, Feb, Mar

Q4 = Apr, May, Jun

 

I want to use those columns to make a formula that checks what Quarter we are in and decide if its Current or Previous, anything else be BLANK. e.g. for right now it would show Current as This Year Q1 and Previous as Last Year Q4.

 

Any ideas?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi paulfink ,

     

    Create a calculated column.

    FYQ = 
var FYM = EDATE('Table'[date],-6)
return
FORMAT(FYM,"yyyyq")

    Then create two measures.

    current = CALCULATE(MAX('Table'[FYQ]),FILTER('Table',FORMAT('Table'[date],"yyyymm")=FORMAT(TODAY(),"yyyymm")))

previous = CALCULATE(MAX('Table'[FYQ]),FILTER('Table',FORMAT('Table'[date],"yyyymm")=FORMAT(EDATE(TODAY(),-3),"yyyymm")))

     

    Best Regards,

    Jay

2 Replies