Forum Discussion

Cx2's avatar
Cx2
New Member
1 year ago
Solved

Distinct count from previous month

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. 

Data Modeling
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    1 year ago

    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!