cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Posfog
Helper I
Helper I

Convert Date to Week Number (number format)

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
 
1 ACCEPTED SOLUTION

Accepted Solutions
kbilakanti
Resolver II
Resolver II

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

View solution in original post

12 REPLIES 12
rebeccas
Community Champion
Community Champion

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

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
Resolver II
Resolver II

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

View solution in original post

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

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

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
Advocate I
Advocate I

 <rem

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 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,653)