Count rows excluding the one with a certain value in any earlier months than selected month

[autogenerate23]

Dear PBI community and experts, 

I would like to count the rows in a column called "Assessment" in my table for a selected month by a slicer. The count should exclude the rows that is equal to "Completed" in any earlier months than the selected month. for the sake of clarity, if the selected month rows happen to have the value of "Completed", i would like to include them in the count. 

To provide further details, I have the following columns in my table. Both are in the same table: 

- Month, like January 2023, Feburary 2023 and so forth and i have created a slicer based on this 

- Assessments which can be "No action", "Ongoing", or "Completed" 

I have tried with the following DAX but niether has worked: 

CountFilteredValues =

VAR SelectedMonth = SELECTEDVALUE('DataTable'[Month])

RETURN

CALCULATE(

COUNT('DataTable'[Assessment]),

FILTER(

'DataTable',

'DataTable'[Month] = SelectedMonth ||

(

'DataTable'[Month] <= SelectedMonth &&

NOT('DataTable'[Assessment] = "Completed")

)

)

)

CountFilteredValues =

VAR SelectedMonth = SELECTEDVALUE('DateTable'[Month])

RETURN

CALCULATE(

COUNT('DataTable'[Assessment]),

FILTER(

'DataTable',

'DataTable'[Month] = SelectedMonth ||

(

'DataTable'[Month] <= SelectedMonth &&

NOT('DataTable'[Assessment] = "Completed")

)

)

)

They do not work in the sense they do not exclude the rows which have "Completed" in any earlier months than selected month. For example, I have 100 rows in total for January 2023, and 5 of them turned "Completed" By applying above measures, I still got 100 rows when selecing Feburary 2023. What i hope to see here is 95. Please kindly advise what i seem to be doing wrong and how to solve this. Thank you very much!   

[autogenerate23]

Apologies for bothering... i am still hoping to get an advice here. If anyone can give a hint on what i may be doing wrong, please kindly advise. Thank you so much!

[amitchandak]

@autogenerate23 , In such cases the the slicer should be on an independent date table. Prefer date than month or use YYYYMM in formula from the same date table

VAR SelectedMonth = Maxx(allselected(IndDate) 'IndDate'[Date])

RETURN

CALCULATE(

COUNT('DataTable'[Assessment]),

FILTER(

'DataTable',

eomonth('DataTable'[Date] ,0) = eomonth(SelectedMonth,0) ||

(

'DataTable'[Date] <= eomonth(SelectedMonth,0)  &&

NOT('DataTable'[Assessment] = "Completed")

)

)

)

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[autogenerate23]

Dear Amitchandak, 

thank you so much for our advice, greatly appreciated. I have craeated an independent date table for slicer which is disconnected from all other tables and applied your Dax measure. Unforutnately, it did not give desired result in the sense that it excludes “completed” from selected month whereas it should actually exclude “Completed” rows for any months earlier than selected month. To illustrate what happned -  I have 100 rows in total for January 2023, and 5 of them turned "Completed" by the end of Jan. By applying the provided measure, I got 95 rows when selecting January 2023 and 100 rows for February 2023. What i rather hope to see is 100 rows for January 2023 and 95 rows (i.e., total 100 rows - 5 rows that turned “Completed" in January 2023) for February 2023. 

Please kindly help me to modify the measure to get the desired behavior where i see 100 rows for January 2023 and 95 rows (i.e., total 100 rows - 5 rows that turned “Completed” in January 2023) for February 2023 when using the slicer. Once again thank you so much for your kind help!