Forum Discussion

dtartaglia's avatar
dtartaglia
Icon for Resolver I rankResolver I
9 years ago

How to count rows in a specific month/year

Hi, I'm trying to create a measure that can count the number of rows found within a specific month and year. Thanks for any help!        
Need Help
dtartaglia's avatar
dtartaglia
Icon for Resolver I rankResolver I
9 years ago

Hi,

 

Unless someone knows a better way, I created month and year columns from my date column then used this DAX statement:

 

Current Month =
CALCULATE(COUNTROWS(vwServiceDeskWorkOrder), vwServiceDeskWorkOrder[ResolvedMonth] = 8,
FILTER(vwServiceDeskWorkOrder, vwServiceDeskWorkOrder[ResolvedYear] = 2017)
)

  • AlbertoFerrari's avatar
    AlbertoFerrari
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    You could do that with a simpler measure:

     

    =CALCULATE (
    COUNTROWS ( YourTable ),
    MONTH ( YourTable[Date] ) = 8,
    YEAR ( YourTable[Date] ) = 2008
)

    Not that your solution is a wrong one (besides, at query time your solution is faster on very large models), just to note that you could do that with a measure. :)


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

    • dtartaglia's avatar
      dtartaglia
      Icon for Resolver I rankResolver I
      9 years ago

      Thanks! I thought I tried that but it didn't work for me. I'll verify again.

    • skidambi2's avatar
      skidambi2
      Frequent Visitor
      7 years ago

      Hi Alberto ,

       

      For the below solution this will give only for the month 8th .... Now if i have to present for whole year what is the measure ? can you please advice >?

       

      • AlbertoFerrari's avatar
        AlbertoFerrari
        Icon for Most Valuable Professional rankMost Valuable Professional
        7 years ago

        Well, just remove the month part, not that complex :)