How to compare to the axis value in a cross table?
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.
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!