topic Re: If date is in between two dates if condition not giving proper results. in DAX Commands and Tips

If date is in between two dates if condition not giving proper results.

Anonymous — Tue, 19 Mar 2024 00:01:14 GMT

In the image left side is my table data and the right side i am expecting to display the data when my date is in between start date and end date,

If my date is > Startdate && my date <= ENd date it has to give result as 1 else 0 .

as shown in the image matrix date.

Can any on ehlp me how do i do that.

Re: If date is in between two dates if condition not giving proper results.

Anonymous — Tue, 19 Mar 2024 04:49:42 GMT

Hi Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 = CALENDAR( DATE(2024,2,1), DATE(2024,2,20))

2. Create measure.

Step1 = var _min= MINX(FILTER(ALL('Table'),'Table'[TestNO]=MAX('Table'[TestNO])&&'Table'[ProjectNo]=MAX('Table'[ProjectNo])),[StartDate]) var _max= MAXX(FILTER(ALL('Table'),'Table'[TestNO]=MAX('Table'[TestNO])&&'Table'[ProjectNo]=MAX('Table'[ProjectNo])),[End Date]) var _mingroup= MINX(FILTER(ALL('Table'),'Table'[TestNO]=MAX('Table'[TestNO])),[StartDate]) var _maxgroup= MAXX(FILTER(ALL('Table'),'Table'[TestNO]=MAX('Table'[TestNO])),[End Date]) return CONCATENATEX( FILTER(ALL('Table 2'), 'Table 2'[Date]>=_min&&'Table 2'[Date]<=_max),'Table 2'[Date],"-" )

Step2 = var _step2= CONCATENATEX( FILTER(ALL('Table'), 'Table'[TestNO]=MAX('Table'[TestNO])),[Step1],"-" ) return IF( CONTAINSSTRING( _step2,MAX('Table 2'[Date]))=TRUE(),1,0)

3. Result:

Here's a discovery that needs to be explained to you, according to your rules, [TestNo] also belongs to 1 on 2/6/2024

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: If date is in between two dates if condition not giving proper results.

Anonymous — Tue, 19 Mar 2024 18:58:35 GMT

Thank you very very much. 👏

Re: If date is in between two dates if condition not giving proper results.

Anonymous — Sat, 23 Mar 2024 17:53:04 GMT

I am unable to fill with 1's in between start date and end date.I think i am missing something.I am unable to attach Pbi file here. Please help me to resolve this.

Re: If date is in between two dates if condition not giving proper results.

Anonymous — Sat, 23 Mar 2024 17:54:33 GMT

First Step

First_Step =

var _min=MIN(tblTeeCards[Tee_Start])

var _max=MIN('tblTeeCards'[Tee_End])

VAR Dates= MIN('Table 2'[Column])

VAR Concat=

CONCATENATEX(

FILTER(ALL('Table 2'),

'Table 2'[Date]>=_min&&'Table 2'[Date]<=_max),'Table 2'[Date],"-"

)

RETURN

Concat

Second Step

Second_Step =

var _step2=

CONCATENATEX(

FILTER(ALL('tblTeeCards'),

'tblTeeCards'[Owning_Panel]=MAX('tblTeeCards'[Owning_Panel])),[First_Step],"-"

)

return

IF(

CONTAINSSTRING(

_step2,MAX('Table 2'[Column]))=TRUE(),1,0)

Re: If date is in between two dates if condition not giving proper results.

Anonymous — Sat, 23 Mar 2024 17:57:06 GMT

If you see this image, i could able to concateneate dates between start and end dates, the dates are perfect but i am unable to fill with 1's when date matching