Distinct count of instances where the date is last month

[Anonymous]

Hi all, 

I 'm trying to create a measure that will give me the distinct count of instances where the date is last month. 

Could you please help?

In the example below it should result 3

Thank you

[Anonymous]

Create a measure with this:

Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Key] );
    MONTH ( 'Table'[Start date] )
        = MONTH ( TODAY () ) - 1
)

Hope it helps!

[Anonymous]

Thank you @Anonymous  for getting back to me.

Unfortunately that solution doesn't work for me, because I have duplicated values in both columns, that the computer can't handle.

"Too many arguments were passed to the DISTINCTCOUNT function. The maximum argument count for the function is 1."

Any idea on how to dedupe those? Start date is a calculated column. 

Thanks again

[Anonymous]

@Anonymous  Could you share a your PBIX with the example? It should work

[Anonymous]

Yes sure @Anonymous , how should I do it?

[Anonymous]

Share it with onedrive or dropbox or something.

Regards

[Anonymous]

hi @Anonymous 

here it is:

https://1drv.ms/u/s!AqJUpeVVowTZgb5Ih7BAuN-4q-3IYw?e=74XVVJ

I have removed any irrelevant columns etc

thank you very much

[Anonymous · Accepted solution]

Try this .  IF not, please tell me what is the spected result from the file.

Regards.

[Anonymous]

Hi @Anonymous 

Thanks again for responding.

Applying this formula on a dataset that includes data from 2 years, it doesn't distiguish this year from previous one. 

I tried adding the year in the formula but didn't work:

Count =

Var current_month= YEAR(TODAY()) && MONTH(TODAY())-1

Return

CALCULATE( DISTINCTCOUNT(Logs[Key]), FILTER(Logs, MONTH(Logs[Start date])= current_month ) )

Do you have any suggestion?

Thank you

[Anonymous]

Try using :

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

Count =

CALCULATE( DISTINCTCOUNT(Logs[Key]),
DATESINPERIOD(Logs[Start date],
LASTDATE(Logs[Start date],-1,MONTH)

)

[Anonymous]

brilliant, thank you very much @Anonymous . 

I'm so grateful!

[Anonymous]

Hello-

   I only see 2 dates in your list that are August.  But the best way to do this would be to create a new column in PowerQuery.  Add a column and extract "After delimiter."  Select /.  You might need to do it twice since there are 2 /'s.  Then you can do a DAX measure for distinct count of that new measure.

Jared

[Anonymous]

hi @Anonymous 

Thank you for your reply. Last month in this case is September since today is October.

I'm not sure why you mention August. Please have in mind this just a sample and there are thousands of rows.

I am hoping to have a measure that I will not need to update every year

[Anonymous]

Ah you're right, I forgot! But in any case, try this solution.  It should work for you.

Jared