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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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 

 

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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

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

7 REPLIES 7
foodd
Super User
Super User

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.

ryan_mayu
Super User
Super User

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




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

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!




Thank you again for the solution @ryan_mayu 

you are welcome





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

Proud to be a Super User!




Thank you @ryan_mayu 

Thank you for your help.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors