Total Last Fiscal Year Mesure

[Anonymous]

Hi all, Big Respect to Everybody.

This is another question about Calendar/Date DAX Measures.

I work with fiscal years starting from the 1st of April.

So, if it is 01 April 2012 - it is first day of Fiscal 2013 for me.

I want to build a Report that shows common Measures - MTD, LYMTD, FYTD and so on.

Report Will Slice measures by date, not by month or year.

Thanks to this forum and Youtube Videos I built almost all measures I need.

My biggest problem Right now is "Revenue Last Full Year"

Actually I have it in my sample, but it is bad, it is not scalable and works for only one year - I made it through Last Fiscal Year to Date, giving it last day of the year.

Revenue Last Full Year = CALCULATE ( [Revenue LFYTD];'Calendar'[CDate]=DATE(2012;3;31))

I have an arrow on Page 1 of my Report showing the trouble measure.

On the totals by Fyear sheet I show the sums that I want to see for this measure.

What is the best way to write it?

Thank You very much!

My Sample File

[Ashish_Mathur]

Hi,

In the Totals by Fyear tab, the Revenue for F2011 is 62,58,26,756.74.  This number exactly matches with your figure for the "Revenue last full year" measure.  Where is the problem?  What exact number are you expecting for "Revenue last full year"?

[Anonymous]

Hi Asnish,

You are right, that is the value I want to get.

but I can not use it, because the only way I could figure it out:

Revenue Last Full Year = CALCULATE ( [Revenue LFYTD];'Calendar'[CDate]=DATE(2012;3;31))

 - I hard coded the fyear.

So, when the fiscal year changes (slicers or just year passes), this value remains the same unlike other measures, that switch automatically.

I do not know how to make measure for full last fiscal year, so it could work by itself, that is what I am asking.

Thank You.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=CALCULATE([Revenue],DATESBETWEEN('Calendar'[CDate],DATE(YEAR(MAX('Calendar'[CDate]))-1,4,1),DATE(YEAR(MAX('Calendar'[CDate])),3,31)))

Hope this helps.

[Anonymous]

Yes, that is exactly what I needed!

Thank You very much!

[Ashish_Mathur]

You are welcome.