Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Right - So I have a table similar to this
Name | client | policy | Year Opened | Month Opened | Year Closed | Month Closed |
Jim | 1 | 1 | 2017 | 5 | NULL | NULL |
Bob | 2 | 1 | 2017 | 5 | 2018 | 2 |
Bob | 2 | 2 | 2017 | 6 | NULL | NULL |
Harry | 3 | 1 | 2017 | 5 | 2018 | 2 |
Tim | 4 | 1 | 2017 | 5 | 2017 | 8 |
How can I code a measure to say how many policies were open at a certain point in time?
Thanks in advance!
Solved! Go to Solution.
Hello,
so let's try to translate your SQL term:
COUNTROWS(
FILTER(Table,
AND([DateSelected]>=[DateOpened],
OR([DateSelected]<=DateClosed,ISBLANK(DateClosed))
)
)
)
Hello, you should create a column DateOpened and DateClosed.
Then you can use something like:
Measure= COUNTROWS(FILTER(Table,[DateSelected]>=[DateOpened]&&[DateSelected]<=DateClosed))
DateSelected=MAX([DateList])
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]))
DateSelected=MAX(Year_Month[Datelist])
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)
Hello,
so let's try to translate your SQL term:
COUNTROWS(
FILTER(Table,
AND([DateSelected]>=[DateOpened],
OR([DateSelected]<=DateClosed,ISBLANK(DateClosed))
)
)
)
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.
Thanks.
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |