Forum Discussion

sbalish's avatar
sbalish
Frequent Visitor
7 years ago

Distinct count total over date range

When using DISTINCTCOUNT to get total unique numbers over a date range (4 months for example)...the total count when adding up all 4 months shown on the chart is higher (incorrect) compared to the co...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Assuming you have:

  1. A Calendar Table with a relationship from the Date column of your Tasks Table to the Date column of your Calendar Table
  2. In the Calendar Table there will be a Year and Month column
  3. To your visual, you have dragged Year and Month from the Calendar Table

Write these measures

Distinct tasks = DISTINCTCOUNT('Tasks'[Number])

Distinct tasks final = COUNTROWS(FILTER(SUMMARIZE(VALUES('Tasks'[Number]),'Tasks'[Number],"Count since inception",CALCULATE([Distinct tasks],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MAX(Calendar[Date]))),[Count since inception]=1))

Hope this helps.

  • sbalish's avatar
    sbalish
    Frequent Visitor
    7 years ago

    Hi! Thanks for the tip.  Yes to all 3 assumptions.  Added the Distinct Task Final measure...error popped up expecting another argument for the FILTER command...I think it needs a FilterExperssion?  Also...it was underlining the [Count since inception]=1 part at the end...do I need to add that as a new column in my table?

     

    Thanks!

    • sbalish's avatar
      sbalish
      Frequent Visitor
      7 years ago

      I see it...needed another right ) just after the MAX(Calendar[Date] part...

       

      Distinct tasks final = COUNTROWS(FILTER(SUMMARIZE(VALUES('Tasks'[Number]),'Tasks'[Number],"Count since inception",CALCULATE([Distinct tasks],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar[Date]),Calendar[Date]),MAX(Calendar[Date])))),[Count since inception]=1))

       

      Unfortunately this measure returns the same count totals.

      Tasks Started Final (417...still incorrect)

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        Share the link from where i can download your PBI file.