cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Posfog
Level: Powered On

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
Level 8

Re: Convert Date to Week Number (number format)

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

5 REPLIES 5
Super User
Super User

Re: Convert Date to Week Number (number format)

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

Posfog
Level: Powered On

Re: Convert Date to Week Number (number format)

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
Level 8

Re: Convert Date to Week Number (number format)

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

Posfog
Level: Powered On

Re: Convert Date to Week Number (number format)

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

kbilakanti
Level 8

Re: Convert Date to Week Number (number format)

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

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,418)