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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors