FY to Date and Previous fiscal Year to date

[PierreL69]

Hello, community,

So sorry it's a recurring topic, but after reading many blogs/posts I still haven't been able to achieve what I wanted to achieve.

I want to be able to display the current Fiscal year to date and related previous fiscal year to date, thus based on the same ending date.

The criteria:

• My fiscal year: Start the 1st October close end of September (30th )
• The date ending the period of the current fiscal year is the day before the present day.

          Here in our case, today's date is 28/05/2020 so the date ending the fiscal year is 27/05/2020.

So with two measures, I would like to have the current FYTD and previous FYTD

Measure:            Period

Measure 1:         N             ->            Current FY to D should sum between 01/10/2019 to 27/05/2020

Measure 2:         N - 1       ->            Previous FY to D should sum between 01/10/2018 to 27/05/2019

Measure 2:         N - 2       ->            Previous FY to D should sum between 01/10/2017 to 27/05/2018

Measure 2:         Etc….

So you will find my pbix file with my tested measures.

• FY Qty = TOTALYTD([Total Qty];Data[Date];"30/9")
• FY Qty V2 = CALCULATE([Total Qty]; DATESYTD(Data[Date];"30/9") )
• FY Previous Qty = CALCULATE([FY Qty];DATEADD('Data'[Date];-1;YEAR))
  • Note this measure should sum quantity for the whole FY
  • 01/10/2018 to 30/09/2019
  • 01/10/2017 to 30/09/2018
  • Etc….

• FYTD Previous QTY = CALCULATE([FY Qty];SAMEPERIODLASTYEAR(Data[Date]))
  • Note this measure should sum quantity for the previous FY to Date
  • 01/10/2018 to 27/05/2019
  • 01/10/2017 to 27/05/2018
  • Etc….

But nothing works correctly

In the PBIX file there is already a calendar with I think all we need in terms of date:

• Date (Date format)
• Year (Number format)
• Year & Month (Text format YYYY-MM)
• Day num (Number format)
• FY Name (Text format)
• FY Month (Number format)
• FY Num (Number format)

Expected results are the following ones :

I remind you that in our case, today's date is 28/05/2020 so the date ending the fiscal year is 27/05/2020

but that's bound to change as the days go by.

FY	Qty	Previous FY	FYTD	Previous FYTD
FY20	166 222	198 824	166 222	129 765
FY19	198 824	184 742	129 765	121 435
FY18	184 742	117 025	121 435	60 337
FY17	117 025		60 337

Then the idea will be to compare the periods but I should get away with the good basic measures

Here the related pbix file 

Thank you very much for your help

[v-alq-msft · Accepted solution]

Hi, @PierreL69 

There is no caldendar table in your data model. I'd like to suggest you define a separate Date table. Generally, your data model will contain a calendar table. It is usually better to aggregate data by year and month using columns of a calendar table (containing one row for each day) instead of extracting the date parts from a single column of type date or datetime in calculated columns. There are a few reasons for this choice. You obtain a model wherein all date attributes are included in a separate table making it easier to browse data using a generic client, and you can use special DAX functions that perform time intelligence calculations. Moreover, most of the time intelligence functions require a separate Date table to work correctly.

You may create a calendar table with calendar or calendarauto function.

Best Regards

Allan

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

[PierreL69]

Hello Allan I am currently working in this direction,

I have created a calendar, with my FY, everything is fine on this side.

I also created a measure to have previous FY, but I am encountering some issues

- about grand total which are not showing a good results, but so far the results are not as expected.

I am currently trying to overcome this with the formula HASONEVALUE but I need to understand how it is working

- about FY over FY evolution that doesn't work 😞 maybe I need to use the divide function to set the denominators to zero, which may be causing my measurement to malfunction

so I'm moving forward, but at a very slow pace.