Distinct count from previous month

[Cx2]

Hey all,

Pretty new to PowerBI which has been implemented in my work - and have my first issue that I can't seem to resolve via ChatGPT!

Essentially I have a dataset of employees which have all been given their own assigned ID (for distinct count reasons)

What I am trying to do is basically have the total distinct IDs of a month and add it together with the total of distinct IDs from 12 months prior, I.e. April 2024 total + April 2023 total. 

I am able to get both of these totals separately in my "Totals" column however in a separate column which adds the current month and the month 12 months prior, it is just repeating the same total as the current month. 

Any advice would be much appreciated and happy to provide details as best I can. 

[Uzi2019]

Hi @Cx2 

Current month count = CALCULATE(

    DISTINCTCOUNT('Table'[ID]),DATEADD('Calendar'[Date],1,MONTH))

Prior month count=  CALCULATE(

    DISTINCTCOUNT('Table'[ID]),DATEADD('Calendar'[Date],-1,year))

Total count= Current month count + Prior month count

You can create seperte 3 measure or you can combine them in single measure using variable.

Total count=
Var Current month count= CALCULATE(

    DISTINCTCOUNT('Table'[ID]),DATEADD('Calendar'[Date],1,MONTH))

Var Prior month count=  CALCULATE(

    DISTINCTCOUNT('Table'[ID]),DATEADD('Calendar'[Date],-1,year))

Return Current month count + Prior month count

I hope I answered your question!

[Anonymous · Accepted solution]

Hi @Cx2 ,

Please share sample data as well as your expected results, but you can try the following expression in general:

DistinctCountPerMonth = DISTINCTCOUNT('YourTable'[EmployeeID])
DistinctCountLastYear = 
CALCULATE(
    DISTINCTCOUNT('YourTable'[EmployeeID]),
    SAMEPERIODLASTYEAR('YourTable'[Date])
)
TotalDistinctCount = 
[DistinctCountPerMonth] + [DistinctCountLastYear]

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.