Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

To find distinct no from same column

Hi all need help to find the distinct count from a same column between two dates. In the below data May month has X1,X2,X3,X4 &X5 and June month has X1,X2,X3 & X6. So X6 is the new number and the count is 1 for the month of June. Kindly help.
DateTool

01-05-2020X1
01-05-2020X2
01-05-2020X3
01-05-2020X4
01-05-2020X5
20-06-2020X1
20-06-2020X2
20-06-2020X3
20-06-2020X6

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    harshagraj try following measure

     

    New Count = 
VAR __firstDate = EOMONTH ( MAX ( Table[Date] ), -1 ) + 1
VAR __prevTools = CACLULATETABLE ( VALUES ( Table[Tool] ), Table[Date] < __firstDate )
RETURN
CALCULATE ( 
COUNTROWS ( Table ),
EXCEPT ( VALUES ( Table[Tool] ), __prevTools )
)

     

    Most likely need bit of tweaking but yo have the gist.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

     

  • parry2k's avatar
    parry2k
    6 years ago

    harshagraj don't remember but change it like this

     

    New Count = 
VAR __firstDate = EOMONTH ( MAX ( Table[Date] ), -1 ) + 1
VAR __prevTools = CACLULATETABLE ( VALUES ( Table[Tool] ), Table[Date] < __firstDate )
RETURN
CALCULATE ( 
DISTINCTCOUNT ( Table[Tool] ),
EXCEPT ( VALUES ( Table[Tool] ), __prevTools )
)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

10 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    harshagraj try following measure

     

    New Count = 
VAR __firstDate = EOMONTH ( MAX ( Table[Date] ), -1 ) + 1
VAR __prevTools = CACLULATETABLE ( VALUES ( Table[Tool] ), Table[Date] < __firstDate )
RETURN
CALCULATE ( 
COUNTROWS ( Table ),
EXCEPT ( VALUES ( Table[Tool] ), __prevTools )
)

     

    Most likely need bit of tweaking but yo have the gist.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

     

      • harshagraj's avatar
        harshagraj
        Icon for Post Partisan rankPost Partisan
        6 years ago
        14-01-2020 
        14-01-20201944653
        14-01-20201829492
        14-01-20201829990
        14-01-20201949860
        14-01-20201949861
        14-01-20201880739
        14-01-20201880739
        14-01-20201880739
        14-01-20201880894
        14-01-2020xxxxxxx
        31-01-2020 
        31-01-20201944653
        31-01-20201829492
        31-01-20201947977
        31-01-20201949296
        31-01-20201951637
        31-01-20201952268
        31-01-20201952269
        31-01-20201952270
        31-01-20201952360
        21-02-20201949786
        21-02-20201949786
        21-02-20201952878
        21-02-20201952878
        21-02-2020513002
        21-02-20203704900
        21-02-20201880816
        21-02-20201950063
        21-02-2020xxxxxxx
        28-02-2020 
        28-02-20201944653
        28-02-20201945212
        28-02-20201945212
        28-02-20201945212
        28-02-20201945212
        28-02-20201945212
        28-02-20201945212
        28-02-20201945213
        28-02-20201945213
        28-02-20201949786
        28-02-20201949786
        28-02-20201949786
        28-02-20201952878
        28-02-20201952878
        28-02-2020513002
        28-02-20203704900
        28-02-20201880816
        28-02-20201950063
    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Hi parry2k  i have a small problem now. Can this calculation be done on Tool context? Like All except (Tool).
      The problem what is happening is in the month of Nov 2019 when i export the data i have only 81 tool no's but when i check in Power BI as count of tool it shows 266.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        harshagraj not fully sure what you are looking for, can you share sample excel file and expected output. Thanks!