cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GAW
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

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
Solution Sage
Solution Sage

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

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?

GAW
Frequent Visitor

DateSS.PNG

GAW
Frequent Visitor

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)

 

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

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

 

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors