Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
cottrera
Post Prodigy
Post Prodigy

DAX last date in months

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 

 

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

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] )

vkaiyuemsft_0-1721291697145.png

 

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.

View solution in original post

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

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] )

vkaiyuemsft_0-1721291697145.png

 

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
Super User
Super User

@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)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.