Calculate total based on previous whole calendar month

[Anonymous]

I have a measure which is a distinctcount of user number, and I have split this into the current reporting month (last whole calendar month) and previous reporting month (the calendar month before) so I can do a KPI.

The previous reporting month measure is working. The current measure is completely wrong.

Here is my measure, are you able to tell me what could be the issue?

= CALCULATE(DISTINCTCOUNT('Table'[USER_NBR]),MONTH('Table'[Date])=month(TODAY())&&YEAR('Table'[Date]=YEAR(TODAY())))

[az38]

Hi @Anonymous 

how do you calculate previous month?

I see missing ")" in YEAR('Table'[Date] statement

try

= CALCULATE(DISTINCTCOUNT('Table'[USER_NBR]),MONTH('Table'[Date])=month(TODAY())&&YEAR('Table'[Date])=YEAR(TODAY()))

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Anonymous]

Ok including the parenthesis is giving me November data, not October. But at least you got it working in some way!

[az38]

@Anonymous today is november 🙂

you wrote that you need current month, but previous works ok

for previous try

= CALCULATE(DISTINCTCOUNT('Table'[USER_NBR]),MONTH('Table'[Date])=month(dateadd(TODAY(),-1,MONTH))&&YEAR('Table'[Date])=YEAR(dateadd(TODAY(),-1,MONTH)))

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Anonymous]

Hi,    

When I say current reporting month I mean the latest whole month so current for me, is October 2019. Previous month is September 2019.

So I can't get an October one working...

The measure you've given is not happy with the (TODAY() section which it's underlining in red. The error message is 

A function 'DATEADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

This is what I wrote:

= CALCULATE(DISTINCTCOUNT('Table'[USER_NBR]),MONTH('Table'[Date])=MONTH(DATEADD(TODAY(),-1,MONTH))&&
YEAR('Table'[Date])=YEAR(DATEADD(TODAY(),-1,MONTH)))

@az38

[az38 · Accepted solution]

@Anonymous pardon, my bad

try

= CALCULATE(DISTINCTCOUNT('Table'[USER_NBR]),MONTH('Table'[Date])=MONTH(EOMONTH(TODAY(),-1))&&
YEAR('Table'[Date])=YEAR(EOMONTH(TODAY(),-1)))

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Anonymous]

This works!

Thanks @az38  much appreciated 🙂