Data not fetching for TODAY DATE in DAX measure

[saivina2920]

I am creating measure. unfortunately, i have tested the below measure. 

vToday = TODAY()
vCalDate5 = [vToday] - 5
Emp Joined Date =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NUMBER]),FILTER(EMP_TABLE,
(EMP_TABLE[JOINED_DATE] >= [vCalDate5] && EMP_TABLE[JOINED_DATE] <= [vToday])

It will fetch only till yesterday records.

It will not fetch today rcords. what is the problem..?

[V-pazhen-msft · Accepted solution]

@saivina2920 
You can not use duplicates as a calendar table. Create a new table. And create a 1 to many relationship between the new table[date] and the sheet1[date]).

Date Table = Distinct(Sheet1[Date])

Then replace the shee1[date] with DateTable[Date]

Measure = CALCULATE(DISTINCTCOUNT((EMP_TABLE[EMP_NUMBER]),EMP_STATUS = "Active", EMP_WORK = "REGULAR",DATESINPERIOD(DateTable[Date]),TODAY(),-5,DAY))  ==> Not working syntax error.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[HarishKM]

@saivina2920 Hey,
You can use this one .

Kudos will be appriciated .

[HarishKM]

1. @saivina2920 
2. @saivina2920 Try this
3.  
4. Last 5 day = CALCULATE(Values(EMP_TABLE[EMP_NUMBER]),DATESINPERIOD(Sheet1[Date],TODAY(),-5,DAY))
   or 
   Last 5 day = CALCULATE(DISTINCTCOUNT((EMP_TABLE[EMP_NUMBER]),DATESINPERIOD(Sheet1[Date],TODAY(),-5,DAY)) 
   Thanks 

[saivina2920]

Thanks. 

i am using few more filter like EMP_STATUS = "Active" && EMP_WORK = "REGULAR"..

How we can apply the above filter if i use the below measure

CALCULATE(DISTINCTCOUNT((EMP_TABLE[EMP_NUMBER]),DATESINPERIOD(Sheet1[Date],TODAY(),-5,DAY)) 

Note : vToday is the main date function and used in many places..

[HarishKM]

Last 5 day with con =

Var last5 =DATESINPERIOD(Sheet1[Date],TODAY(),-5,DAY)

Return

CALCULATE(DISTINCTCOUNT((EMP_TABLE[EMP_NUMBER]),EMP_STATUS = "Active", EMP_WORK = "REGULAR",last5)

[saivina2920]

CALCULATE(DISTINCTCOUNT((EMP_TABLE[EMP_NUMBER]),EMP_STATUS = "Active", EMP_WORK = "REGULAR",DATESINPERIOD(Sheet1[Date],TODAY(),-5,DAY))  ==> Not working syntax error.

Error : A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'. This is not supported.

[saivina2920]

sorry. i don't undertand your measure.

can you correct us which i given in the post..?

if i give 

vToday = DATE(2021,4,6) ==> it will fetch till today. but. today date is "5". i am giveing "6" (2016,4,6)

if i give 

vToday = DATE(2021,4,5) ==> it will fetch till yesterday...how it is possible.???

[ryan_mayu]

@saivina2920 

i think your last condition

EMP_TABLE[JOINED_DATE] >= [vCalDate5] && EMP_TABLE[JOINED_DATE] <= [vCalDate5])

is the same as

(EMP_TABLE[JOINED_DATE] = [vCalDate5]

is there something wrong with this part?

[saivina2920]

Yes. Sorry. i wrongly mentioned the measure. below is the one which i used.

vToday = TODAY()
vCalDate5 = [vToday] - 5
Emp Joined Date =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NUMBER]),FILTER(EMP_TABLE,
(EMP_TABLE[JOINED_DATE] >= [vCalDate5] && EMP_TABLE[JOINED_DATE] <= [vToday])

problem is here ==> EMP_TABLE[JOINED_DATE] <= [vToday])...????