cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Common Math problem: for calculating Fiscal Quarter Offset

I need help to use a M formula to calculate [Fiscal Qtr Offset], based on [Fiscal Mth].  Would really appreciate if you can help!

 

Fiscal yearFiscal MthFiscal Qtr Offset
F20191-5
 2-5
 3-5
 4-4
 5-4
 6-4
 7-3
 8-3
 9-3
 10-2
 11-2
 12-2
F20201-1
 2-1
 3-1
 40
 50
 60
 71
 81
 91
 102
 112
 122
F202113
 23
 33
 44
 54
 64
 75
 85
 95
 106
 116
 126
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: Common Math problem: for calculating Fiscal Quarter Offset

Thanks for the suggestion @v-frfei-msft 

I have a much larger date table generated using PQ, so I was hoping to look for a PQ solution.

 

I was able to solve the issue - hope this will help others in the future:

= Number.RoundDown(((Date.Year([Date]) - Date.Year(CurrentDate)) * 12 + Date.Month([Date]) - Date.Month(CurrentDate)) / 3, 0)​

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Common Math problem: for calculating Fiscal Quarter Offset

@donaldm 

 

Need little more explanation on how you are generating Fiscal Qtr Offset? The Current quarter will always be -1?

Connect on LinkedIn
Highlighted
Community Support
Community Support

Re: Common Math problem: for calculating Fiscal Quarter Offset

Hi @donaldm ,

 

We can achieve that by DAX.

offset = -6+CALCULATE(DISTINCTCOUNT('Table'[qtr]),FILTER(ALL('Table'),'Table'[Date]<=EARLIER('Table'[Date])))

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Helper III
Helper III

Re: Common Math problem: for calculating Fiscal Quarter Offset

Thanks for the suggestion @v-frfei-msft 

I have a much larger date table generated using PQ, so I was hoping to look for a PQ solution.

 

I was able to solve the issue - hope this will help others in the future:

= Number.RoundDown(((Date.Year([Date]) - Date.Year(CurrentDate)) * 12 + Date.Month([Date]) - Date.Month(CurrentDate)) / 3, 0)​

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.