Calculating rolling numbers based on day of the week

[KW123]

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. 

[v-jianboli-msft · Accepted solution]

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.

[KW123]

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! 

[KW123]

@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

[KW123]

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

[KW123]

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! 

[v-jianboli-msft]

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.

[KW123]

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.
 

[v-jianboli-msft]

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.

[KW123]

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

[Anonymous]

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))

[KW123]

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?