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

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 Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,384)