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
 
12 REPLIES 12

Worked like a charm!! Thank you!!

Hi,

 

The Formula is working but, 2021 is 1 week behind. Instead of showing WK07 it show WK06 - does it need to be change every year?

Thanks 

Almog

 

@amoshe2  the other solution posted here may work for your use case. The solution that ScottDrow gave me works for my use case where the first week of the year is that with Jan 1st regardless of the # of days of the new year in that week. I attempted to paste it below but for 2 days in a row now this page is giving me an "invalid HTML" error

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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,435)