Default Slicer to Specific Quarter

[Anonymous]

Hello, 

I am working on a report that needs to default to the current year's previous quarter by default. Is there a simple way to achieve this by default in a slicer? I have seen methods that use a date table to default to the current day's date, but for this slicer, the logic would have to account for the current year, the current quarter, and then default to the previous quarter. 

Sample Workbook

Thanks in advance!

[Greg_Deckler]

@Anonymous - You do that by creating a column similar to below. PBIX is attached below sig. Table (20).

Quarter = 
  VAR __Year = YEAR(TODAY())
  VAR __Current = QUARTER(TODAY())
  VAR __Previous = __Current - 1
  VAR __PreviousQuarterYear = 
    SWITCH(TRUE(),
      __Current = 1,__Year - 1,
      __Year
    )
  VAR __PreviousQuarter = 
    SWITCH(TRUE(),
      __Current = 1,4,
      __Current - 1
    )
RETURN
  SWITCH(TRUE(),
    YEAR([Column1]) = __PreviousQuarterYear && QUARTER([Column1])=__PreviousQuarter,"Last Quarter",
    __Year & "-" & FORMAT(__Current,"00")
  )

[Anonymous]

@Greg_Deckler  - Would an independent date table still be necessary if I used this method? 

[Greg_Deckler]

@Anonymous No. Also, I updated the post with the PBIX file and corrected DAX syntax. 

[Anonymous]

@Greg_Deckler  - Oh okay, great, thank you! I think those are the wrong workbooks though. 

[Greg_Deckler]

@Anonymous Sorry, I was only supposed to upload Working68.PBIX and you want Table (20). Let me know if you see it. Sorry, I didn't use your PBIX, faster to just use my working copy.

[Anonymous]

@Greg_Deckler  - No worries, thank you! 

Why are dates in January and March categorized as 2020-03 instead of Q1? 

[Greg_Deckler]

@Anonymous Because I'm tired apparently, here it is corrected:

Quarter = 
  VAR __Year = YEAR(TODAY())
  VAR __Current = QUARTER(TODAY())
  VAR __Previous = __Current - 1
  VAR __PreviousQuarterYear = 
    SWITCH(TRUE(),
      __Current = 1,__Year - 1,
      __Year
    )
  VAR __PreviousQuarter = 
    SWITCH(TRUE(),
      __Current = 1,4,
      __Current - 1
    )
RETURN
  SWITCH(TRUE(),
    YEAR([Column1]) = __PreviousQuarterYear && QUARTER([Column1])=__PreviousQuarter,"Last Quarter",
    YEAR([Column1]) & "-" & FORMAT(QUARTER([Column1]),"00")
  )

[Anonymous]

Thank you and thanks for helping when it's so late! 

I actually just realized I have a date modified column and not a column that contains the date of the actual quarter. There is a column with the quarters (Q1, Q2, etc.) and I need to make a column that evaluates the Year column and then looks at the Quarter column that would determine if it is the current year and current quarter, switch to the last quarter or use that new column to somehow achieve that. 

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi @v-rzhou-msft , 

Thank you for following up! Everyone has been very helpful, but I actually need a bit more guidance. Please see my previous post.

Thank you

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I think your quarter is not the quarter in date format, it is the column in your example.

You want to get the default Ex: If today = 2020/09/17 Quarter = Q4 so you want the default result to show the sum of values in Q3.

I  add a rank column in your table and build a measure to achieve your goal.

Rank = IF(Sheet1[Quarter]=BLANK(),BLANK(),RANKX(Sheet1,Sheet1[Quarter],,ASC,Dense)-1)

Build a slicer table:

Slicer = VALUES(Sheet1[Quarter])

Measure:

Measure = 
var _sel = SELECTEDVALUE(Slicer[Quarter])
var _rank = CALCULATE(MAX(Sheet1[Rank]),FILTER(Sheet1,Sheet1[DateModified]=TODAY()))
var _previous = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,Sheet1[Rank]=_rank-1))
var _selResult = CALCULATE(SUM(Sheet1[Value]),FILTER(Sheet1,Sheet1[Quarter]=_sel))
return
IF(ISFILTERED(Slicer[Quarter]),_selResult,_previous)

Result:

Due to there isn't any value which date modified = 2020/09/17 so it shows blank.

Select slicer the tree visual will show the value whose quarter = Selected.

You can download the pbix file from this link: Default Slicer to Specific Quarter

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , You need to have Qtr start and end date for that. In your date table. Then you can create a column like this

qtr Type =
Var _lastq = date(year(today()), month(Today())-3,1)
Switch( True(),
[start qtr]<=Today() && [end qtr]>=Today(),"This qtr" ,
[start qtr]<=_lastq && [end qtr]>=_lastq,"Last qtr" ,
[qtr Name]
)

and select Last qtr .

This video :https://youtu.be/Qt0TM-4H09U

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

Show start of qtr calc