Existing 4-4-5 date table and using it to filter on previous quarter

[Anonymous]

My company uses a 4-4-5 Fiscal year, which translates into non-standard quarter start dates. I have created a comprehensive (I think) date table that accounts for the fiscal years through 2030, which includes as some of its fields:

1. Fiscal Quarter (e.g., "Q1", "Q2", "Q3", "Q4")
2. Fiscal Quarter Number (e.g., 1, 2, 3, 4)
3. Fiscal Quarter Sequence (e.g., 1, 2, 3, 4, 5, 6, 7...). This increments with each new quarter regardless of year.

I have marked this table as a date table. While I can find plenty of material to tell me how to CREATE date tables, I have thus far been unable to find examples showing me how to USE custom date tables the way I would have expected in visuals, filters, etc. Most examples I've found seem to reference the built-in date table, which is of litte use to me.

OBJECTIVE:  Filter a table of data based on the previous fiscal quarter, based on a date field called "Opp_Close_Date". 

How do I create a picker that essentially accomplishes:  "if the opp close date falls in the previous fiscal quarter, then display it in the table." 

I sense that I'm missing something fundamental.

[v-yiruan-msft]

Hi @Anonymous ,

Could you please share some sample data(exclude sensitive data) in your date table & fact table with field [opp close date] and your expected result with examples in order to provide you a suitable solution? Thank you. 

For example:

1. Date table

Date	Fiscal Quarter	Fiscal Quarter Number	Fiscal Quarter Sequence

2. Fact table

Col_1	Col_2	opp close date	Col_...	Col_x

Expected result:

Exp_col1	Exp_col..	Exp_colx

Best Regards

[amitchandak]

@Anonymous , Based on what I got

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)  // I usually this but you have Fiscal Quarter Sequence
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Fiscal Quarter Sequence]=max('Date'[Fiscal Quarter Sequence])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Fiscal Quarter Sequence]=max('Date'[Fiscal Quarter Sequence])-1))

Last Qtr for some other date, Inactive join

Last Qtr = CALCULATE(CALCULATE(sum('order'[Qty]), USERELATIONSHIP(Employee[Start Date],'order'[Opp_Close_Date]))

FILTER(ALL('Date'),'Date'[Fiscal Quarter Sequence]=max('Date'[Fiscal Quarter Sequence])-1))

also check

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

All non standard stuff can be deal like week, if you can have rank or sequence

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8