How to compare to the axis value in a cross table?

Hi there,

 

I have a cross table which horizontal axis is a date hierarchy "Year.Month" and for the cell values I need to compare the value in the axis to 2 other date columns to know if I should count the value on the corresponding line or not. Actually I need to do this to count certain values only between two dates in the table. Then, giving a table with the columns [start date], [end date] and [count value] I would like to do something like :

UniqueCount(CASE WHEN [start date] < Date([Axis.Columns]) and [end date] > Date([Axis.Columns]) THEN [count value] ELSE NULL END)OVER(AllPrevious([Axis.Columns]))

Is there anyway I, can do this? So far I always had errors, either type errors or unfound function errors.

 

EDIT:

Actually I need to do this to count certain values only between two dates in the table

Let's say that I have two lines:

Line 1: start date= 01.01.2012              end date = 10.10.2014               mydate = 05.07.2013

Line 2: start date = 01.01.2013            end date = 12.05.2015               my date= 12.12.2014

 

Then I want to count line 1 from 05.07.2013 to Oct. 2014 but not anymore after Oct. 2014.

Thanks in advance for your help!

(1) Answer

Login