cancel
Showing results for
Did you mean:
Highlighted
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 year Fiscal Mth Fiscal Qtr Offset F2019 1 -5 2 -5 3 -5 4 -4 5 -4 6 -4 7 -3 8 -3 9 -3 10 -2 11 -2 12 -2 F2020 1 -1 2 -1 3 -1 4 0 5 0 6 0 7 1 8 1 9 1 10 2 11 2 12 2 F2021 1 3 2 3 3 3 4 4 5 4 6 4 7 5 8 5 9 5 10 6 11 6 12 6
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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)​
3 REPLIES 3
Highlighted
Super User II

## Re: Common Math problem: for calculating Fiscal Quarter Offset

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

Highlighted
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])))

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

## 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)​

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.

Top Solution Authors
Top Kudoed Authors