Right - So I have a table similar to this
How can I code a measure to say how many policies were open at a certain point in time?
Thanks in advance!
Go to Solution.
so let's try to translate your SQL term:
COUNTROWS( FILTER(Table, AND([DateSelected]>=[DateOpened], OR([DateSelected]<=DateClosed,ISBLANK(DateClosed)) ) ))
View solution in original post
Hello, you should create a column DateOpened and DateClosed.
Then you can use something like:
DateList has to a table with distinct dates, first of month should be enough. No relationship necessary.
Where does the Dateselected get defined?
I have created the datelist and posted the following into a new Measure but it's saying the syntax for DateDelected is incorrect
Is DateSelected it's own measure?
Measure = COUNTROWS(FILTER(OpenMattersatPointinTime,[DateSelected]>=[Open Date]&&[DateSelected]<=[Closed Date]))
Thanks for the assist.
Can you make a screenshot of your DateList?
Thanks for you help so far onm this but I don't think your solution will do what I need.
What I am trying to achieve using my example table above as a visual aid is if I select 04/01/2011 it will tell me I had 3 policies still open (Bob2, Harry and Tim)
If I select 07/01/2011 it will say 1 (Bob2)
In simple SQL terms:
@Dateselected = my date filter
if date_opened < @Dateselected
AND (date_Closed IS NULL
OR date_closed > @Dateselected)
Sorry for being a little slow on the uptake - When you say [DateSelected] is that the Datelist we created earlier in another table?
Is so I have created this following from your example
OpenMatters = COUNTROWS( FILTER(OpenMattersatPointinTime, AND(year_month[Datelist]>=OpenMattersatPointinTime[Open Date], OR(year_month[Datelist]<=OpenMattersatPointinTime[Closed Date], ISBLANK(OpenMattersatPointinTime[Closed Date]]) ) ) ) )
OpenmattersatPointinTime is table1 containing our date
Year_Month is table2 containing our date list in field [Datelist]
This returns the error "The end of the input was reached" and when I accept the formual it adds a bunch of close brackets to the end but still shows the error.
Accept the formula again and you get yet more close brackets.
I googled the error and it says it's because of missing brackets but I pasted into notepad.net and everything matches up.
Ignore me - I think I found the problem (tjhere was a mysterious square bracket floating in the syntax)
It now looks looks like this:
As a learning exercise I am also trying to do the same thing using nested Filters
Just struggeling to get the Or statement into the second Filter.
Read the announcement for more information!
We're rolling out new Kudos Given badges. Find out how many Kudos you've given.
Get an overview of the events and great community content from November.
Find out where you can attend!