Hi,

I have a simple 2 column dataset representing Date of session and Client Name (Please download workbook from here - http://1drv.ms/1J6NKUd).  The Financial Year runs from April 1 to March 31.  I want to determine the number of sessions conducted in chosen financial year - 2.  So if I select FY 2015-16 in a slicer, then, I want view the number of session conducted client wise in FY 2013-14.  This is the formula calculated field formula I have written but when I drag this in the Pivot Table, I get an error message.  In the calculated field window, when I click on Check for errors, then there are no errors at all.

=CALCULATE([Sessions conducted],DATESBETWEEN(calendar[Dates],EDATE(FIRSTDATE(calendar[Dates]),-24),EDATE(FIRSTDATE(calendar[Dates]),-12)-1))

Hi,

Please help.

Hi,

never used EDATE before, so cannot tell why it doesn't work here.

How about this alternative:

:=CALCULATE(

SUM(sessions_data[Spalte1]);

DATESBETWEEN(calendar[Dates];

FIRSTDATE(DATEADD(calendar[Dates];-24;month));

FIRSTDATE(DATEADD(calendar[Dates];-12;month))))

Hi,

That does not work either.  I get the same error message as was with the previous formula - "An invalid numeric representation of a data value was encountered".

Please try it on the file I shared and you will realise.

Then my modification to the DateTable must have made the difference - have a look:

https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!10961&parId=DE165DDF5D02DAFF!107&app=Excel