Define fiscal year in a measure

[Appe]

Hello,

The client I'm working with just changed their fiscal year from the normal calendar year to july - june and I need to make some changes in Power BI accordingly. We've been using the following formula to summarize the budget:

FSG Budget FO = CALCULATE(sum('Anso Goal TG'[FSG]);
year('Anso Goal TG'[Goal startdate]) = year(now()); month('Anso Goal TG'[Goal startdate]) < month(now()))

However since year now is defined by the calendar year I need to make change the formula to correspond with the new fiscal year. The strucutre of the formula above is used in some additional places so it would probably be best to define the fiscal year somehow to be able to re use the logic.

Any ideas?

Best,

Appe

[v-qiuyu-msft]

Hi @Appe,

Assume the fiscal year is from July 1st to June 30th of the next year. You can create a calculated column below: 

Fiscal Year = IF(MONTH('Table'[Date]) <=6,YEAR('Table'[Date]),YEAR('Table'[Date]) +1)

Then replace the year(now()) with this new column Fiscal Year. 

Best Regards,
Qiuyun Yu 

[Appe]

Hi @v-qiuyu-msft,

Thank you for the reply!

In your solution, would it be something like IF(MONTH('accounts'[DateWon]) <=6,YEAR('accounts'[DateWon]),YEAR('accounts'[DateWon]) +1)? So i'd re use the same date throughout the formula? 

Best,

Anders

[v-qiuyu-msft]

Hi @Appe,

Yes, you need to create a new column to return fiscal year based on DateWon column in your scenario, then use this new column in FSG Budget FO formula. 

Best Regards,
Qiuyun Yu