topic Re: Calculating rolling numbers based on day of the week in DAX Commands and Tips

Calculating rolling numbers based on day of the week

KW123 — Sat, 09 Jul 2022 04:49:00 GMT

I have a calculated column which goes from 1-X based on the number of days in the month.

FD = CALCULATE(SUM('Date'[FD]),

FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date]

)))

I am trying to build an IF statement that says when the row falls on a Friday/Saturday, make the number the same.

So if I have a colum that goes:

Day of week Calculated column
Monday 1
Tuesday 2
Wednesday 3
Thursday 4

Friday and Saturday would both be 5. Skip Sunday, and the following Monday would be 6 and so on. The first number in the column is dependant on the first day of the week of the month. So it will always start at one, no matter the day of the week. I have a date table which has the days of the week already converted into numerical values. 1=Sunday, 7= Saturday.

Re: Calculating rolling numbers based on day of the week

Anonymous — Sat, 09 Jul 2022 08:06:12 GMT

Hi KW123,

Please try this code

day rank = if(WEEKDAY(Feuil2[Date]) in {6,7},5,if(WEEKDAY(Feuil2[Column4])<>1,WEEKDAY(Feuil2[Date])-1))

Re: Calculating rolling numbers based on day of the week

KW123 — Mon, 11 Jul 2022 16:39:39 GMT

Hi JamesFR06

Thank you for the sugguestion! It returns each Friday and Saturday a value of 5 which is good except I need the numbers to keep going up as the month goes.

1
2
3
4
5
5
6
7
8
9
10
10

etc....
Is there a way to do that?

Re: Calculating rolling numbers based on day of the week

v-jianboli-msft — Tue, 12 Jul 2022 05:59:40 GMT

Hi KW123 ,

Please try:

Calculate Column =

SUMX ( FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) && WEEKDAY ( EARLIER ( 'Table'[Date] ), 2 ) <> 7 ), IF ( WEEKDAY ( [Date], 2 ) <= 5, 1 ) )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating rolling numbers based on day of the week

KW123 — Tue, 12 Jul 2022 16:48:31 GMT

Hi Jianbo,

Thank you! Yes this is exactly what I am looking for. However for me it returns values that do not start at 0. They start at 655. I have tried the Summarize, don't summarize, and Count but none of them seem to work.

Thank you

Re: Calculating rolling numbers based on day of the week

v-jianboli-msft — Wed, 13 Jul 2022 04:03:16 GMT

Hi KW123 ,

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating rolling numbers based on day of the week

KW123 — Wed, 13 Jul 2022 17:50:27 GMT

Hi Jianbo,

Sorry that the information was not clear.

As you can see in the FD column, the numbers start at 133 and increase in value over time. The data begins at Jan 2020 but I have a slicer in the report that starts at 2022 and would like the FD column to reset to 1 at each month based on the slicer. You can see in the report that I have it set to Jan 2022 and the begins at 655 instead of 1.

Re: Calculating rolling numbers based on day of the week

KW123 — Thu, 14 Jul 2022 20:21:47 GMT

From what I am able to find online, I need to create a measure instead of a column? What would be the DAX for that?

Thank you!

Re: Calculating rolling numbers based on day of the week

v-jianboli-msft — Fri, 15 Jul 2022 07:41:39 GMT

Hi KW123 ，

According to your description, I adjusted the original DAX, please try:

Calculate Column = SUMX ( FILTER ( 'Table', [Date] <= EARLIER ( 'Table'[Date] ) && WEEKDAY ( EARLIER ( 'Table'[Date] ), 2 ) <> 7 && MONTH ( [Date] ) = MONTH ( EARLIER ( 'Table'[Date] ) ) && YEAR ( [Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) ) ), IF ( WEEKDAY ( [Date], 2 ) <= 5, 1 ) )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating rolling numbers based on day of the week

KW123 — Fri, 15 Jul 2022 15:17:03 GMT

v-jianboli-msft Thank you so so much this worked!!

Re: Calculating rolling numbers based on day of the week

KW123 — Fri, 15 Jul 2022 17:20:40 GMT

Hi v-jianboli-msft

Is there a way to have this go in Descending order? I need one column to go from 1-X and another to go from X-1. If I change the second column (FD2) to descending, they both change and nothing happens in the report. I hope that makes sense.

Basically looking for a column that is "days left" from the first one we just created. Thank you so much!

Re: Calculating rolling numbers based on day of the week

KW123 — Fri, 15 Jul 2022 19:31:10 GMT

v-jianboli-msft
Sorry, another question! I have a Holiday table. Is there a way to encorporate that into the DAX you gave me earlier? If the FD falls on a holiday, to make it the same number as the previous day?

Feb
Wednesday 16=12FD
Thursday 17 =13FD
Friday 18= 14
Saturday 19 =14
Monday 21 (Holiday) =14