DATESINPERIOD not giving results

[GLIDO]

Hello Members,

While trying to fetch the number of leads for the last 6 months using the datesinperiod function I am unable to get results. 

A have used a date field "DATE_OFRECEIPT" as a reference table, no doubt it contains duplicate records, but i cant figure out why it is obstructing the calculation. Can anyone explain this to me ? Can we have a workaround?

[Interkoubess · Accepted solution]

Hi @GLIDO,

What about creatting a Calendar Table ( containing unique values for every day) covering your dates and link it to your fact table and then use the formula with DateinPeriod ( on Calendar date and not on "DATE_OFRECEIPT").

That should work....

Otherwise please share sample data and we can figure out...

Ninter

[GLIDO]

Hi @Interkoubess Ninter, 

Thanks for the reply. 

Actually the code that I've written in the question snap reads 

Leads6Months = CALCULATE(COUNT(LOCAL_CAS_DUMP_TBL_MIS_V01[APP_ID]),DATESINPERIOD(LOCAL_CAS_DUMP_TBL_MIS_V01[DATE_OF_RECEIPT],MAX(LOCAL_CAS_DUMP_TBL_MIS_V01[Start Date]),-6,MONTH))

While I was trying to find different ways to get the solution, before you answered this question, 

I got the solution using the following code, 

Leads6Months = CALCULATE(COUNT(LOCAL_CAS_DUMP_TBL_MIS_V01[APP_ID]),DATESINPERIOD(LOCAL_CAS_DUMP_TBL_MIS_V01[DATE_OF_RECEIPT].[Date],MAX(LOCAL_CAS_DUMP_TBL_MIS_V01[Start Date]),-6,MONTH))

I just added an extra .[Date]. 

Do you have any ideas on how this happened and what is the significance of these. 

[v-yuezhe-msft]

@GLIDO,

I am unable to reproduce this error using my sample data. Could you please share sample data of your table here? Also as other's post, it is better to create a calendar table containg unique date values and then use DATESINPERIOD function.

Regards,
Lydia