How to calculate distinct count of last month and last to last month

[Parvez933]

Hi!

Can anyone please help in writing a DAX to calculate the Previous Month/Last Month, and Last to last month/last to previous month

Example:

Month ID

Jan       12

Jan        23

Feb      24

Feb      34

Mar      44

Mar      32

calculate(distinctcount(ID), feb???)

calculate(distinctcount(ID), jan???)

We tried

previousmonth() got Blank,

datesinperiod giving result for Feb but not for Jan

parelleperiod

currentmonth month(today())-1

but mostly not expected results

[v-jingzhang · Accepted solution]

Hi @Parvez933 

Previousmonth, datesinperiod and parelleperiod are all time intelligence functions which require a date column. Do you have date column in your table or only have the month name column as the sample data shows? In addition, if the first month in your table is January, the distinct count number of its previous month will be blank as there is no previous month data for it. Do you want to show blank for the earliest month or show other value for it?

Based on the current sample data, you can add a month number column to the table, then use the following measure to get the distinct count number of previous month for every month. 

Previous Count = 
VAR vCurrentMonth = MAX('Table'[MonthNumber])
RETURN
CALCULATE(DISTINCTCOUNT('Table'[ID]),ALL('Table'[Month]),'Table'[MonthNumber]=vCurrentMonth-1)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.