Convert Date to Week Number (number format)

[Posfog]

Hi all,

Thanks for taking the time to read this.

I am new to PowerApps and PowerBi and have this following issue that i would like some advice if possible.

I have the following formula in PowerApps that converts the Date to the text "WK" and then the working week number.

"WK" & RoundDown((RoundUp (
DataCardValue10.SelectedDate - Date(
Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),
1,
3
),
0
) + Weekday(
Date(
Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),
1,
3
)
) + 5) / 7,
0
)

This in itself works fine, with all the data going to sharepoint, but when i am pulling the data into a PowerBi report the slicer i use for the Week Number puts the weeks into a format that is unuseful 

Eg

WK1

WK11

WK13

WK2

WK3

WK33

WK4

Is there a way that the formula above would add a leading zero to any week number between 1 and 9 as i believe this would sort the issue in PowerBi

kind Regards

[rebeccas]

What if you just store that column as a number instead and take off the "WK" part?

[Posfog]

Hi There,

I was getting the same issue with SharePoint column being set as a number only.

The outcome in powerbi was still doing the same 

EG

1

2

21

24

26

3

34

4

5

7

[kbilakanti · Accepted solution]

Hi,

Please use this formula, to make your solution working.

"WK" & If(Len(Text(RoundDown((RoundUp (

DataCardValue10.SelectedDate - Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

),

0

) + Weekday(

Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

)

) + 5) / 7,

0

)))=1,"0"&RoundDown((RoundUp (

DataCardValue10.SelectedDate - Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

),

0

) + Weekday(

Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

)

) + 5) / 7,

0

),RoundDown((RoundUp (

DataCardValue10.SelectedDate - Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

),

0

) + Weekday(

Date(

Year(DataCardValue10.SelectedDate - Weekday(DataCardValue10.SelectedDate - 1) + 4),

1,

3

)

) + 5) / 7,

0

))

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Posfog]

Ive just doubled check this and any date after march and it doesnt work for some reason?

[kbilakanti]

I have modified the formula and updated the post and it should work now. Please check once.

[srosenbloom]

I see how this works if the first day of the week is Monday. How do you adjust the following if the first day of the week is Sunday?

RoundDown(
     (RoundUp (
          Now() - Date(
               Year(Now() - Weekday(Now() - 1) + 4),
               1,
               3
          ),
          0
     ) + Weekday(
          Date(
               Year(Now() - Weekday(Now() - 1) + 4),
               1,
               3
          )
     ) + 5) / 7,
     0
)

Thanks,

Scott

[mguerra]

 <rem

[mguerra]

Hi, do you know how to modify your formula for calendars where the first week of the year is the week where January 1st falls? Using your formula as-is gives me that 1/1/2021 is 20WW53 when in my company it should be 21WW01. Thank you. @kbilakanti 

[ScottDurow]

You can use the fact that Value(Today()) returns an integer that is in milliseconds, so the following gives you the week number - just change the StartOfWeek.Monday to whatever you need

    RoundUp((
        ((Value(Date(Year(varDate),Month(varDate),Day(varDate))) - Value(Date(Year(varDate),1,1))) / 86400000) // Days into year
        + Weekday(Date(Year(varDate),1,1),StartOfWeek.Monday) // Offset for day of week on 1st Jan
    ) / 7 // Divide by days in a week
    ,0) // Round Up to get the week number (e.g. if 2.5 - it means we are half way through the 3rd week, so return 3)

 hth,

Scott