Calculate special fiscal year to date with the same beginning date & month

[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. 

Sample Data will be 

Date	Value	FYTD Value
2023-11-01	100	100
2023-12-01	100	200
2024-01-01	100	300
2024-02-01	100	400
2024-03-01	100	500
2024-04-01	100	600
2024-05-01	100	700
2024-06-01	100	800
2024-07-01	100	900
2024-08-01	100	1000
2024-10-01	100	1100
2024-11-01	50	1150

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]

Thanks for the reply from marcelsmaglhaes , please allow me to provide another insight:

Hi  @ljx0648 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _table1=
SUMMARIZE(
    ALL('Table'),[Year_Month],"mindate",MINX(FILTER(ALL('Table'),[Year_Month]=EARLIER('Table'[Year_Month])),[Date]),"Sum_Value",SUMX(FILTER(ALL('Table'),[Year_Month]=EARLIER('Table'[Year_Month])),[Value]))
var _table2=
ADDCOLUMNS(
    _table1,"YTD_Value",
IF(
    YEAR([Year_Month])=2024,
    SUMX(
        FILTER(_table1,[mindate]>=DATE(YEAR([mindate])-1,11,1)&&[mindate]<=DATE(YEAR([mindate]),11,1)&&[mindate]<=EARLIER([mindate])),[Sum_Value]),
    SUMX(
        FILTER(_table1,[mindate]>=DATE(YEAR([mindate]),1,1)&&[mindate]<=DATE(YEAR([mindate]),10,31)&&[mindate]<=EARLIER([mindate])),[Sum_Value])))
RETURN
SUMX(
    FILTER(_table2,[Year_Month]=MAX('Table'[Year_Month])),[YTD_Value])

As far as I know, Power BI default sorting is alphabetical, so [Year_Month] won't follow the table order to achieve that, we need to create a table to be used as sort_table to sort.

2. Create calculated table.

Sort_Table =
SUMMARIZE(
    'Table',[Year_Month],"mindate",MINX(FILTER(ALL('Table'),[Year_Month]=EARLIER('Table'[Year_Month])),[Date]))

3. Select [Year_Month] – Column tools – Sort by column – [mindate].

4. Joins the relationship between two tables.

5. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[marcelsmaglhaes]

Hey @ljx0648 ,

You’ll need to check if the current fiscal year is 2024. If it is, you calculate the FYTD up to November 1, 2024; otherwise, you use the standard FYTD calculation ending on October 31.


FYTD_Sales_Special =
IF (
MAX(Datetable[Fiscal Year]) = 2024,
CALCULATE(
SUM(Salestable[Sales]),
DATESBETWEEN(Datetable[Date], DATE(2023, 11, 1), DATE(2024, 11, 1))
),
CALCULATE(
SUM(Salestable[Sales]),
DATESYTD(Datetable[Date], "31/10")
)
)

[ljx0648]

Thank you so much for your suggestion Marcel.

I tried the formula you suggested and got the result below:

Year_Month	Value	YTD Value
Jun-23	1	1
Jul-23	1	2
Aug-23	1	3
Sep-23	1	4
Oct-23	1	5
Nov-23	1	1
Dec-23	1	1
Jan-24	1	1
Feb-24	1	1
Mar-24	1	1
Apr-24	1	1
May-24	1	1
Jun-24	1	1
Jul-24	1	1
Aug-24	1	1
Sep-24	1	1
Oct-24	1	1
Nov-24	1	1

As you can see, the year_month before Nov 2023 really did add up as FYTD Value. However, the month after that doesnt add up.

May I know if you can offer any tips to modify it?

Much appreciated

[marcelsmaglhaes]

Hey @ljx0648 ,

Maybe you can try this updated dax:

FYTD_Sales_Special =
IF (
MAX(Datetable[Fiscal Year]) = 2024,
CALCULATE(
SUM(Salestable[Sales]),
DATESBETWEEN(
Datetable[Date],
DATE(2023, 11, 1),
DATE(2024, 11, 1)
)
),
CALCULATE(
SUM(Salestable[Sales]),
DATESYTD(Datetable[Date], "31/10")
)
)