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
Super User
Super User

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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