Power BI Desktop - DAX measure

[ShubhamTrivedi]

Hello Power BI community,

I am trying to calculate how many distinct calls a manager makes to a contact in the last month by using this DAX formula - 

DistinctContactsPrevMonth =

VAR PrevMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1) 

VAR PrevMonthEnd = EOMONTH(TODAY(), -1) 

VAR AdjustedPrevMonthEnd = PrevMonthEnd + TIME(23, 59, 59)

RETURN

CALCULATE(

    DISTINCTCOUNT(phonecall[contactid]),

    FILTER(

        crm_act_phonecall,

        DATEVALUE(phonecall[CreatedOn]) >= PrevMonthStart &&

        DATEVALUE(phonecall[CreatedOn]) <= AdjustedPrevMonthEnd 

    )

)

The total numbers that I see in the table by using this measure is +1 for all the managers. For e.g. in the real data when I check - Manager A has made 200 calls in the previous month but in the power BI using this measure I get 201 for the same manager. Same case for all the managers. 

I have also converted the createdon column in the power bi as just the date column which previously was a datetime column. Even when this was datetime column the results were like this and now when I have converted this column as just date column - same results. Someone pls help me as the data is not accurate. 

Kind regards,

[V-yubandi-msft]

Hi @ShubhamTrivedi ,
Please accept your own solution as the answer so it will be easier for other community users to identify it.

[V-yubandi-msft]

Hi @ShubhamTrivedi ,
Thank you for the update. I'm good to hear you resolved the issue. If you have any more questions or want to discuss further, please keep using the Microsoft Fabric Community Forum.

Thank you for your prompt reply @MFelix .

Regards,

Yugandhar.

[MFelix]

Hi @ShubhamTrivedi ,

 A couple of questions:

• Do you have a calendar table?
  • Yf yes is it related with the CreatedON date?
• You are adjusting the VAR prevMonth with the time to get time date time format correct?
• The selection of your visuals are based on month/year selection or on date?

If you create a Date Table and related it with the CreatedOn column (or the date column of the Phonecall table), be sure to not do a date/time format then you can use a similar

DistinctContactsPrevMonth =
CALCULATE(DISTINCTCOUNT(phonecall[contactid]), 
         DATEADD(Calendar[Date], -1 , Month)
)

This should return the correct result.

[ShubhamTrivedi · Accepted solution]

Hi @MFelix 

Thanks for your reply. I have fixed the issue. There was an error in my table. 

Once again, thanks for your help.

Kind regards,