cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gr8mt10
New Member

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

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.

2 REPLIES 2
yingyinr
Community Support
Community Support

Hi @gr8mt10 ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User IV
Super User IV

@gr8mt10 , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors