DAX last date in months

[cottrera]

Hi I have a fact table with adate column and reference number this is connected to a calendar table.  I require a measure that counts all the rows for the end date of every month. Below is an example of a table visual they dispplays date and count of rows. The third column is and example of what I am tryingto achieve.

thank you 

Date	Count	Count Last Date of Month
01/01/2024	15,270
02/01/2024	15,033
03/01/2024	15,762
04/01/2024	15,007
05/01/2024	15,172
06/01/2024	15,395
07/01/2024	15,448
08/01/2024	15,012
09/01/2024	15,641
10/01/2024	15,574
11/01/2024	15,477
12/01/2024	15,702
13/01/2024	15,257
14/01/2024	15,140
15/01/2024	15,020
16/01/2024	15,582
17/01/2024	15,406
18/01/2024	15,738
19/01/2024	15,606
20/01/2024	15,763
21/01/2024	15,156
22/01/2024	15,640
23/01/2024	15,548
24/01/2024	15,615
25/01/2024	15,676
26/01/2024	15,733
27/01/2024	15,523
28/01/2024	15,576
29/01/2024	15,789
30/01/2024	15,053
31/01/2024	15,069	15,069
01/02/2024	15,286
02/02/2024	15,435
03/02/2024	15,592
04/02/2024	15,208
05/02/2024	15,149
06/02/2024	15,503
07/02/2024	15,293
08/02/2024	15,355
09/02/2024	15,058
10/02/2024	15,358
11/02/2024	15,340
12/02/2024	15,127
13/02/2024	15,302
14/02/2024	15,545
15/02/2024	15,783
16/02/2024	15,554
17/02/2024	15,324
18/02/2024	15,679
19/02/2024	15,403
20/02/2024	15,323
21/02/2024	15,775
22/02/2024	15,162
23/02/2024	15,074
24/02/2024	15,722
25/02/2024	15,290
26/02/2024	15,292
27/02/2024	15,212
28/02/2024	15,143
29/02/2024	15,229	15,229

[v-kaiyue-msft · Accepted solution]

Hi @cottrera ,

Thanks for the reply from @bhanu_gautam , please allow me to provide another insight: 

You can create calculated columns to achieve the goal.

Column = 
VAR _end =
    EOMONTH ( 'Table'[Date], 0 )
RETURN
    IF ( 'Table'[Date] = _end, 'Table'[num] )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@cottrera , First make sure that your calender table has End of Month column

Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),

Then create a measure for Count of rows

Count of Rows = COUNTROWS('FactTable')

Create one measure for Count for last date of month

Count Last Date of Month =
VAR LastDateOfMonth =
CALCULATE (
MAX('Calendar'[EndOfMonth]),
ALLEXCEPT('Calendar', 'Calendar'[Year], 'Calendar'[Month])
)
RETURN
CALCULATE (
[Count of Rows],
'Calendar'[Date] = LastDateOfMonth
)
"Day", DAY([Date]),
"EndOfMonth", EOMONTH([Date], 0)
)