cancel
Showing results 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

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/2024 Monday 0 1/2/2024 Tuesday 0 1/3/2024 Wednesday 0 1/4/2024 Thursday 0 1/5/2024 Friday 1 1/6/2024 Saturday 0 1/7/2024 Sunday 0 1/8/2024 Monday 0 1/9/2024 Tuesday 0 1/10/2024 Wednesday 0 1/11/2024 Thursday 0 1/12/2024 Friday 2 1/13/2024 Saturday 0 1/14/2024 Sunday 0 1/15/2024 Monday 0 1/16/2024 Tuesday 0 1/17/2024 Wednesday 0 1/18/2024 Thursday 0 1/19/2024 Friday 3 1/20/2024 Saturday 0 1/21/2024 Sunday 0 1/22/2024 Monday 0 1/23/2024 Tuesday 0 1/24/2024 Wednesday 0 1/25/2024 Thursday 0 1/26/2024 Friday 4 1/27/2024 Saturday 0 1/28/2024 Sunday 0 1/29/2024 Monday 0 1/30/2024 Tuesday 0 1/31/2024 Wednesday 0

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

2 ACCEPTED SOLUTIONS
Super User

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)

Proud to be a Super User!

Super User

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

Proud to be a Super User!

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

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

Super User

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)

Proud to be a Super User!

Helper III

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

My mistake.

How do I achieve this:

 1/1/2024 Monday 0 0 1/2/2024 Tuesday 0 0 1/3/2024 Wednesday 0 0 1/4/2024 Thursday 0 0 1/5/2024 Friday 1 1 1/6/2024 Saturday 0 1 1/7/2024 Sunday 0 1 1/8/2024 Monday 0 1 1/9/2024 Tuesday 0 1 1/10/2024 Wednesday 0 1 1/11/2024 Thursday 0 1 1/12/2024 Friday 2 2 1/13/2024 Saturday 0 2 1/14/2024 Sunday 0 2 1/15/2024 Monday 0 2 1/16/2024 Tuesday 0 2 1/17/2024 Wednesday 0 2 1/18/2024 Thursday 0 2 1/19/2024 Friday 3 3 1/20/2024 Saturday 0 3 1/21/2024 Sunday 0 3 1/22/2024 Monday 0 3 1/23/2024 Tuesday 0 3 1/24/2024 Wednesday 0 3 1/25/2024 Thursday 0 3 1/26/2024 Friday 4 4 1/27/2024 Saturday 0 4 1/28/2024 Sunday 0 4 1/29/2024 Monday 0 4 1/30/2024 Tuesday 0 4 1/31/2024 Wednesday 0 4 2/1/2024 Thursday 0 0 2/2/2024 Friday 1 1 2/3/2024 Saturday 0 1 2/4/2024 Sunday 0 1 2/5/2024 Monday 0 1 2/6/2024 Tuesday 0 1 2/7/2024 Wednesday 0 1 2/8/2024 Thursday 0 1 2/9/2024 Friday 2 2 2/10/2024 Saturday 0 2

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

Thank you.

Super User

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

Proud to be a Super User!

Helper III

Thank you again for the solution @ryan_mayu

Super User

you are welcome

Proud to be a Super User!

Helper III

Thank you @ryan_mayu