cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GAW Frequent Visitor
Frequent Visitor

DAX help needed - count of records on filtered dates

Right - So I have a table similar to this

 

 

NameclientpolicyYear OpenedMonth OpenedYear ClosedMonth Closed
Jim1120175NULLNULL
Bob212017520182
Bob2220176NULLNULL
Harry312017520182
Tim412017520178

 

How can I code a measure to say how many policies were open at a certain point in time?

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

Re: DAX help needed - count of records on filtered dates

Hello,

 

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

8 REPLIES 8
Floriankx Established Member
Established Member

Re: DAX help needed - count of records on filtered dates

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.

GAW Frequent Visitor
Frequent Visitor

Re: DAX help needed - count of records on filtered dates

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.

Floriankx Established Member
Established Member

Re: DAX help needed - count of records on filtered dates

Can you make a screenshot of your DateList?

GAW Frequent Visitor
Frequent Visitor

Re: DAX help needed - count of records on filtered dates

DateSS.PNG

GAW Frequent Visitor
Frequent Visitor

Re: DAX help needed - count of records on filtered dates

Thanks for you help so far onm this but I don't think your solution will do what I need.

example.PNG

 

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)

 

Floriankx Established Member
Established Member

Re: DAX help needed - count of records on filtered dates

Hello,

 

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

GAW Frequent Visitor
Frequent Visitor

Re: DAX help needed - count of records on filtered dates

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.

GAW Frequent Visitor
Frequent Visitor

Re: DAX help needed - count of records on filtered dates

 

Ignore me - I think I found the problem (tjhere was a mysterious square bracket floating in the syntax)

It now looks looks like this:

 

syntax1.PNG

 

As a learning exercise I am also trying to do the same thing using nested Filters

syntax2.PNG

Just struggeling to get the Or statement into the second Filter.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)