Fiscal Week

[archuleta28]

Hi,

I need to show Fiscal weeks for each month. The year starts from April 2022 and I am able to get fiscal week numbers starting from 1 to 52.  BUT -

it should show week-1, week-2,... for each month instead of the week numbers running up to 52. So to cut short, the new month should start with week-1. 

How can this be achieved?  

[jaweher899]

You can achieve this by creating a calculated column in your data table that calculates the fiscal week number based on the month and year, and then use that column in your visualization. Here's an example of how you can create the calculated column:

1. In Power BI Desktop, open the data table that contains the month and year information.

2. In the Home tab, click on "New Column" to create a new calculated column.

3. In the formula bar, enter the following DAX formula:

= IF( MONTH([Date]) >= 4, WEEKNUM([Date]) - WEEKNUM(DATE(YEAR([Date]), 4, 1)) + 1, WEEKNUM([Date]) + (52 - WEEKNUM(DATE(YEAR([Date])-1, 4, 1))))

This formula uses the WEEKNUM() function to calculate the week number of the date, and the MONTH() and YEAR() functions to determine if the date is in the current fiscal year (starts from April) or the previous fiscal year. If the date is in the current fiscal year, the formula subtracts the week number of the first day of April from the week number of the date. If the date is in the previous fiscal year, the formula adds the difference between the week number of the first day of April and 52 to the week number of the date.

1. Press Enter to create the calculated column.

2. Rename the column to something like "Fiscal Week".

3. Use this column in your visualization to show the fiscal week for each month.

It's also important to note that the formula above is assuming that your date field is named "Date". If it's named something else, you'll need to adjust the formula accordingly.

You can also use the same formula but with a different approach, by creating a variable that would contain the month and year of each date, and then use the variable in the formula to calculate the fiscal week.

[archuleta28]

Hi,

Thanks for your quick resonse, I have implemented your suggestion but it still gives me data in week-1 till week-52.  I need to show weeks in below manner for month filter.

Month: (for selected month)

Week 1	Week 2	Week 3	Week 4	Week 5
value	value	value	value	value

Financial year starts from 1st April. For each month, data should be visible in the above way. So irrespective of the month selected, week number should be dispalyed as week-1 till week5 according to the month's weeks.

[PowerBIEnthu · Accepted solution]

Please follow the steps in following video 
https://youtu.be/K2MxoAyMLYU

[PowerBIEnthu]

you should concatenate Week in you week number like CONCATENATE("Week ", measure giving week number)