Count rows where month and year are the same across multiple date columns

[bernate]

Hello! I am trying to count the number of times a phase has been completed each month. For this example I am looking at December 2022, so the number of times a phase has been completed during that time is 6.

I started with CALCULATE(COUNTROWS(Table),MONTH(Table[Phase1]) = MONTH(Table[Phase 2])) but quickly realized that I'm only matching on the month and not the year, and that I can't include the other phases because the formula is set up to give a true/false response. 

My other thought was to create a calculated table where I append the Phase columns into one and do a count that way, but I was wondering if there would be an easier measure that would get me what I need.

Thanks!

[Daniel29195]

Hello @bernate 

assuming this is the structure of the table you are using, i would suggest to unpivot the table to have the following structure : 

item , phase _ nb , date 

then i would create w date table,  and link the date table to the table above on date . 

this way,  you only need to create w measure  :  countrows(table_name) . 

hope this would help you . 

let me know if you have any concerns, im happy to help .

best regards, 

[aj1973]

Hi @bernate 

You need to create a calculated column and unpivot all phases. Better do it in Power Query and refrence the master table

Please check my solution

https://drive.google.com/file/d/1w3qdBjCnItN8KVrIFuVARctAZ4PcRnKN/view?usp=sharing

Open Power Query

[bernate]

HI Amine, I essentially did the same thing by creating a new table and doing a UNION on the phase columns. Is there no way to create a measure to do all of this instead?