Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)​
v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can achieve that by DAX.

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

 

Pbix as attached.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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