Count rows based on Month and year comparison

[Anonymous]

 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/2019	10/03/2019	Y
01/03/2019	06/05/2015	N
01/04/2019	04/08/2016	N
01/04/2019	20/04/2019	Y
01/04/2019	05/04/2019	Y

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

[Anonymous]

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]

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 · Accepted solution]

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,

[Anonymous]

Hi Cherie, Thank you for the response, your solution was perfect for the question that I asked so I have accepted it as the correct solution.... the only problem is, I asked the wrong question. I will close this thread while I think about it a bit more. Thanks Moby