Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Devtr
Helper III
Helper III

How to add calculated column in the date table to get numbers of occurrence of Fridays in each month

‎06-17-2024 06:10 PM

How do I add calculated column in the date table to get numbers of occurence of Fridays in each month?

How do I achive the result of the third column below? This should be done for each month.

1/1/2024Monday0
1/2/2024Tuesday0
1/3/2024Wednesday0
1/4/2024Thursday0
1/5/2024Friday1
1/6/2024Saturday0
1/7/2024Sunday0
1/8/2024Monday0
1/9/2024Tuesday0
1/10/2024Wednesday0
1/11/2024Thursday0
1/12/2024Friday2
1/13/2024Saturday0
1/14/2024Sunday0
1/15/2024Monday0
1/16/2024Tuesday0
1/17/2024Wednesday0
1/18/2024Thursday0
1/19/2024Friday3
1/20/2024Saturday0
1/21/2024Sunday0
1/22/2024Monday0
1/23/2024Tuesday0
1/24/2024Wednesday0
1/25/2024Thursday0
1/26/2024Friday4
1/27/2024Saturday0
1/28/2024Sunday0
1/29/2024Monday0
1/30/2024Tuesday0
1/31/2024Wednesday0

 

When new month starts, this counting should start with 1.

I cannot come up with anything. I am sorry about that.

Please help.

@rajendraongole1 @Ritaf1983 @amitchandak 

@Ashish_Mathur 

 

Labels:
Message 1 of 8
607 Views
0
2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

‎06-17-2024 06:38 PM

@Devtr 

 

you can try this

 

Column 2 = if('Table'[week]="Friday", countx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&year('Table'[date])=year(EARLIER('Table'[date]))&&month('Table'[date])=month(EARLIER('Table'[date]))&&'Table'[week]="Friday"),'Table'[date]),0)
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Message 2 of 8
571 Views
0
ryan_mayu
Super User
Super User
In response to Devtr

‎06-17-2024 07:07 PM

then try this

 

Column 2 = countx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&year('Table'[date])=year(EARLIER('Table'[date]))&&month('Table'[date])=month(EARLIER('Table'[date]))&&'Table'[week]="Friday"),'Table'[date])+0
 
11.PNG
 
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Message 5 of 8
554 Views
0
7 REPLIES 7
foodd
Super User
Super User

‎06-17-2024 07:32 PM

Hello @Devtr ,    Another Shaped table with [StartDate] and [EndDate] that includes a 'Number of Fridays' column 

using the following DAX would work, though this measure sums the total number of Fridays in a Series of Dates, and not specific to a month or number of Fridays in a specific month.

 

Code is below, and PBIX is attached below Signature.

foodd_0-1718676674557.png

 

Number of Fridays =
VAR NumOfWeeks =
    INT ( DATEDIFF ( [StartDate], [EndDate], WEEK ) )
VAR NumOfFridays =
    COUNTROWS (
        FILTER ( GENERATESERIES ( [StartDate], [EndDate], 1 ), WEEKDAY ( [Value] ) = 6 )
    )
RETURN
    IF ( NumOfWeeks = 0, IF ( WEEKDAY ( [StartDate] ) = 1, 1, 0 ), NumOfFridays )

 

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

NumberFridays.pbix
Message 8 of 8
542 Views
0
ryan_mayu
Super User
Super User

‎06-17-2024 06:38 PM

@Devtr 

 

you can try this

 

Column 2 = if('Table'[week]="Friday", countx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&year('Table'[date])=year(EARLIER('Table'[date]))&&month('Table'[date])=month(EARLIER('Table'[date]))&&'Table'[week]="Friday"),'Table'[date]),0)
 
11.PNG




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 2 of 8
572 Views
0
Devtr
Helper III
Helper III
In response to ryan_mayu

‎06-17-2024 07:03 PM

@ryan_mayu 

That works just fine. But it did not work for my matrix.

My mistake.

How do I achieve this:

1/1/2024Monday00
1/2/2024Tuesday00
1/3/2024Wednesday00
1/4/2024Thursday00
1/5/2024Friday11
1/6/2024Saturday01
1/7/2024Sunday01
1/8/2024Monday01
1/9/2024Tuesday01
1/10/2024Wednesday01
1/11/2024Thursday01
1/12/2024Friday22
1/13/2024Saturday02
1/14/2024Sunday02
1/15/2024Monday02
1/16/2024Tuesday02
1/17/2024Wednesday02
1/18/2024Thursday02
1/19/2024Friday33
1/20/2024Saturday03
1/21/2024Sunday03
1/22/2024Monday03
1/23/2024Tuesday03
1/24/2024Wednesday03
1/25/2024Thursday03
1/26/2024Friday44
1/27/2024Saturday04
1/28/2024Sunday04
1/29/2024Monday04
1/30/2024Tuesday04
1/31/2024Wednesday04
2/1/2024Thursday00
2/2/2024Friday11
2/3/2024Saturday01
2/4/2024Sunday01
2/5/2024Monday01
2/6/2024Tuesday01
2/7/2024Wednesday01
2/8/2024Thursday01
2/9/2024Friday22
2/10/2024Saturday02

 

I am now looking for the fourth column result. And I cannot understand Earlier function.

Please help again.

Thank you.

Message 4 of 8
556 Views
0
ryan_mayu
Super User
Super User
In response to Devtr

‎06-17-2024 07:07 PM

then try this

 

Column 2 = countx(FILTER('Table','Table'[date]<=EARLIER('Table'[date])&&year('Table'[date])=year(EARLIER('Table'[date]))&&month('Table'[date])=month(EARLIER('Table'[date]))&&'Table'[week]="Friday"),'Table'[date])+0
 
11.PNG
 
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 5 of 8
555 Views
0
Devtr
Helper III
Helper III
In response to ryan_mayu

‎06-18-2024 07:33 AM

Thank you again for the solution @ryan_mayu 

Message 6 of 8
520 Views
0
ryan_mayu
Super User
Super User
In response to Devtr

‎06-18-2024 05:34 PM

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Message 7 of 8
507 Views
0
Devtr
Helper III
Helper III
In response to ryan_mayu

‎06-17-2024 06:48 PM

Thank you @ryan_mayu 

Thank you for your help.

Message 3 of 8
562 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All