Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 |
Solved! Go to 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.
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.
@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)
)
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
13 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
20 | |
14 | |
11 | |
10 | |
10 |