fiscal year compared to prior year

[Ria]

I have total costs for the current year compared to the prior year by month.  (PR YEAR = CALCULATE(SUM(DATA[TOTAL]),PARALLELPERIOD(DATES[Date].[Date],-12,MONTH))  

I would like to see this for the fiscal year.  In my DATES table, I have: FiscalYearNumber = If( Month([Date]) >= 7  , Year([Date])+1,Year([Date]) ) and FiscalMonth = (If( Month([Date]) >= 7  , Month([Date]) - 6,Month([Date]) + 6 ))

How do I adjust the PR YEAR to calculate based on my new fiscal year and month?

 

[v-shex-msft]

Hi @Ria,

You can add a calculage column to calculate the fiscal date, then modify your formula based on it.

Sample:

Fiscal Date = DATE(If( Month([Date]) >= 7  , Year([Date])+1,Year([Date])),If( Month([Date]) >= 7  , Month([Date]) - 6,Month([Date]) + 6 ),DAY([Date]))

Regards,

Xiaoxin Sheng

[Ria]

This mostly works except that it does not account for the fact that all months do not have the same number of days.  If my calendar date is Aug 28/YY then the fiscal date is 02/28/YY, but calendar date Aug 29/YY becomes 03/1/YY.  Is there a way to account for this?

[v-shex-msft · Accepted solution]

Hi @Ria,

>>If my calendar date is Aug 28/YY then the fiscal date is 02/28/YY, but calendar date Aug 29/YY becomes 03/1/YY.

By default, the date function will auto convert the date calculation.

I also not found a better solution to solve this issue. In my opinion, I'd like to suggest you to ignore the "day" of fiscal date, only keep the year and month.

Notice: Fiscal date not the real date, you not need to care about the detail day of date. You only need to ensure the original data are in the same month.

Regards,

Xiaoxin Sheng

[Ria]

Good idea.  I made the day the first day of the month so that the month is always correct.

Fiscal Date = DATE(if(month([Date].[Date]) >=7, Year([Date].[Date])+1,year([Date].[Date])),if(month([Date].[Date])>=7, month([Date].[Date]) - 6, Month([Date].[Date]) +6),1)