Single Slicer for Period

[Anonymous]

Hi All,

I have two dates, From and To.

I have created one Dimension Date table for slicer.

Objective is to calculate count if slicer date lye between from and to date.

Here is the Dax for for Count, Its working fine if I have From and To Date available in Grid, but when i remove these columns from Grid so i have wrong count.

SR Count =

var flag =

IF (

SELECTEDVALUE ( 'CompanyInvolvment'[SrFromDate] ) <= MAX ( DimDate[FullDateAlternateKey] )

&& SELECTEDVALUE ( 'CompanyInvolvment'[SrToDate] ) >= MIN ( DimDate[FullDateAlternateKey] )

,

1,0

)

var SRCount =

IF (

flag = 1, CALCULATE(DISTINCTCOUNT('CompanyInvolvment'[SR_Number])) + 0,0

)

return CONVERT(SRCount,INTEGER)

Current selection in slicer is Sep,22. This is correct result when i have dates in Grid

But when i remove Dates from Grid so it becomes 0 There should be Total 3 Count.

This is the issue, i dont want to show Dates in grid.

Any suggestion?

Regards,

[Jayee]

Hi @Anonymous 

try below DAX if the field SR_Number unique to table CompanyInvolvment then it works otherwise need to modify the DAX to solve your requirement.

SR Count =
COUNTX (
CompanyInvolvment,
IF (
SELECTEDVALUE ( 'CompanyInvolvment'[SrFromDate] )
<= MAX ( DimDate[FullDateAlternateKey] )
&& SELECTEDVALUE ( 'CompanyInvolvment'[SrToDate] )
>= MIN ( DimDate[FullDateAlternateKey] ),
1,
0
)
)

[Anonymous]

Hi Jayee, 

Thanks for response.

I modify the DAX and having Unique SR Numbers but still it is having wrong counting as shown below.

Filter selection is septembr,2022 but it is counting some other values as well which is not lying between this date.

Any idea?