Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count rows based on Month and year comparison

 Hi All,

 

I have a single table with 2 date fields:

  • Report Date: The date on which the data table was created... the 1st of every month
  • Created On: The date that each row within the data table was created.

Report Date    Created on    Count

01/03/201910/03/2019Y
01/03/201906/05/2015N
01/04/201904/08/2016N
01/04/201920/04/2019Y
01/04/201905/04/2019Y

The logic I need is: Count IF "Created On" Month and Year = "Report date" Month and Year.

What is the best method of creating this?

Thank you in advasnce for any help.

Moby

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create a column like below and then create a measure to get the count.

    Column = 
IF (
    MONTH ( 'Table'[Report Date] ) = MONTH ( 'Table'[Created On] )
        && YEAR ( 'Table'[Report Date] ) = YEAR ( 'Table'[Created On] ),
    "Y",
    "N"
)

    Measure =
COUNTROWS ( FILTER ( 'Table', NOT ( 'Table'[Analysis] ) IN { "CLSD" } ) )

    Regards,

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Update: I have managed to get to this point by adding 2 columns which format the date into year and month:

    Now I just need to figure out how to write the IF statement to compare the 2 dates and count all rows not including "CLSD".... I'm still struggling, so I would appreciate any help.

     

    • AnthonyTilley's avatar
      AnthonyTilley
      Icon for Solution Sage rankSolution Sage
      7 years ago

      This Calcualted colunm will give you a 1 or a 0 based on the two dates 

      you can then sum this colunm as a measure to get what your after

       

      Column =
      Var Ry = year(Table1[Report Date]) --get report year
      Var rm = month(Table1[Report Date]) --get report month
      Var cy = year(Table1[Created date]) --get created year
      Var cm = MONTH(Table1[Created date]) --get created month
      var checky = ry = cy --compare Report and created year and return true or false
      var checkm = rm = cm --compare Report and created month and return true or false
      -- Check that both results are True if so then 1 else 0
      Var ret = if(and(checkm,checky),1,0)
      Return ret


      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous 

        You may create a column like below and then create a measure to get the count.

        Column = 
IF (
    MONTH ( 'Table'[Report Date] ) = MONTH ( 'Table'[Created On] )
        && YEAR ( 'Table'[Report Date] ) = YEAR ( 'Table'[Created On] ),
    "Y",
    "N"
)

        Measure =
COUNTROWS ( FILTER ( 'Table', NOT ( 'Table'[Analysis] ) IN { "CLSD" } ) )

        Regards,