Calculate Fiscal year to date sales with different dates in three years

[ljx0648]

Dear Community,

My company's fiscal year starts from Nov 1st each year and ends with Oct 31st as the last date every year. For example, Fiscal year 2023 starts from Nov 1st, 2022 and ends with Oct 31st, 2023.

For all the prior years, I am using a Fiscal Year to Date dax formula with no issue:

FYTD_Sales = Calculate (sum(salestable[sales]),DatesYTD(datetable[date],"31/10")

However, I got a special request from my boss:

For Fiscal year 2024, we need to add Nov 1st, 2024 as the last date of the Year WITHOUT impacting all calculations of the prior years.

In other words, For ALL the fiscal prior fiscal 2024, I can still use the formula above. But for fiscal 2024 I will need to create another formula from Nov 1st, 2023 to Nov 1st 2024. THEN for Fiscal 2025, I will need to create another formula from Nov 2nd, 2024, till Oct 31st 2025.

Lastly, the ultimate goal is to put all the THREE fiscal year FYTD # above in one chart for comparison with Month Number on X-axis.

May I know if anyone can share any ideas or insights on how to solve this request?

Any tips are very much appreicated!

Thank you all

[Anonymous · Accepted solution]

Hi, @ljx0648 

It seems that you have gotten a solution in this thread: Solved: Calculate Fiscal Year To Date value using a column... - Microsoft Fabric Community.

Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@ljx0648 

maybe you can try this to create a fiscal year column

fiscalyear = SWITCH(TRUE(),

'date'[Date]<date(2024,11,1) &&month('date'[Date])<11,year('date'[Date]),

'date'[Date]<date(2024,11,1) &&month('date'[Date])>=11,year('date'[Date])+1,

'date'[Date]>=date(2024,11,1) && (month('date'[Date])<11||(month('date'[Date])=11 && day('date'[Date])=1)),year('date'[Date]),

year('date'[Date])+1)

[ljx0648]

Hi Freeman,

May I know how to write the Fiscal Year to Date sales with the fiscal year column you suggested?

I already created a datetable with fiscal year column listed "2023","2024“,"2025" .

Can you please show me a formula using that column to calculate fiscal year to date value?

Thank you

[ryan_mayu]

what do you mean by calculate fiscal year to date value?

[ljx0648]

For example, I am calculating Fiscal Year to Date Sales using this formula as of now:

FYTD_Sales = Calculate (sum(salestable[sales]),DatesYTD(datetable[date],"31/10")

However, this formula use "31/10" (date column) as the last date of the fiscal year and it applies to all other year.

I am looking for a formula that can use the Fiscal Year column you just suggested so I can break them out separately for each fiscal year.

Hope this clarifies the question.

[ryan_mayu]

since now you have the fiscal year column , you can just use fiscal year column to display every year's data. 

[ljx0648]

Hi Ryan,

Please let me show the desired outcome with sample data:

Month	Monthly Sales	FYTD Sales
1	2	2
2	3	5
3	4	9
4	5	14
5	7	21

I can now display the "Month" & "Monthly Sales" using the raw data and the Fiscal Year Column.

However, I am looking for a Formula which can provide me the result for the "FYTD Sales"

Thank you

[ryan_mayu]

How do you want to display  2023/11/1? belongs to month 12 FY 23 or month 1 FY 24?

[ljx0648]

Hi Ryan,

I am hoping to create the table for each fiscal year.

For fiscal 2024, Nov 2023 is Month 1 & Nov 2024 is Month 12

Best regards

[ryan_mayu]

if this is your sample data, what's the expected output?

date	value
2023/8/1	100
2023/9/1	200
2023/10/1	300
2023/11/1	400
2023/12/1	500
2024/8/1	600
2024/9/1	700
2024/10/1	800
2024/11/1	900
2024/12/1	1000

[ljx0648]

Sorry Ryan, Please neglect the one I replied earlier with Nov 1 2024 = 1900.. My brain is not working well in the morning.

Please check the CORRECT number I am looking for using the sample data:

Date	Value	FYTD Value
2023-08-01	100	100
2023-09-01	200	300
2023-10-01	300	600
2023-11-01	400	400
2023-12-01	500	900
2024-08-01	600	1500
2024-09-01	700	2200
2024-10-01	800	3000
2024-11-01	900	3900
2024-12-01	1000	4900

[ryan_mayu]

@ljx0648 

2024-11-01

900

3900

Is this the beginning of a new fiscal year?

[ljx0648]

Hi Ryan,

This is where the special request kicks in. For Fiscal 2024, the date starts from Nov 1, 2023 till Nov 1 2024.

Nov 2, 2024 is the start of new fiscal year

[ryan_mayu]

then why 2024/12/1 is 1900 not 1000?

[ljx0648]

Hi Ryan,

Using the sample data you provided, this is my expected outcome (column FYTD Value):

Date	Value	FYTD Value
2023-08-01	100	100
2023-09-01	200	300
2023-10-01	300	500
2023-11-01	400	400
2023-12-01	500	900
2024-08-01	600	1100
2024-09-01	700	1300
2024-10-01	800	1500
2024-11-01	900	1700
2024-12-01	1000	1900

[FreemanZ]

hi @ljx0648 ,

try to write three measures and plot all in the line chart.

[ljx0648]

Hi Freeman,

May I know how to write the specific measure you suggested?

I already created a datetable with fiscal year column listed "2023","2024“,"2025" .

Can you please show me a formula using that column to calculate fiscal year to date value?

Thank you