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

amitchandak's avatar
amitchandak
Super User
4 years ago

paulfink , For current Qtr and Last qtr you can use time intelligence as these are Qtr start with Jan, or Apr Or Jul Or Oct

 

example

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

 

 

 

other than that m have index on FY year  Qtr like 2020Q1

new column

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense)

 

measure
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

 

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441