Rolling Calendar - Week 53 and assigning a fiscal week number

[Clwin1012]

Hello,

I am relatively new to power bi, struggling to learn dax and visuals.

I wa able to create ( with lots of online video tutorials)  a rolling calendar in Power Bi which includes calendar weeks/months/years as well as corresponding Fiscal Weeks/Months/Years.  It does not take into consideration a week 53, which throws off my week numbers for any type of year over year analysis.

Can someone help me revise the fiscal week number Dax to account for week 53.

Fiscal year runs July 1-June 30th, so in fiscal year 22, December has a calendar week 53, and week 1 for January is off as well showing only 1 day in calendar week 1, but 7 days in fiscal week 27 (which should be fiscal week 28)

Any assistance would be appreciated.

Thanks

Cheryl

[ppm1]

Please see if this calendar table makes this easier. It includes columns like fiscal week that should simplify the DAX needed for comparisons.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[Clwin1012]

Thanks Pat,

I watched the video and to be really honest, I don't understand how to use the "M" code and/or how to apply it to the calendar dates I am using.  My power bi abilities are limited, just learning DAX.

thank you for the suggestion, I do appreciate it, just beyond me how to use it.

[Clwin1012]

Hello,

I tried the formula above and the results were not as expected.  

my calendar starts on 7/1/2018 and automatically refreshes daily with todays date added.

here are the results I received;

I could send the table in a power bi, just can't see where to attach it here.

thanks

Cheryl

[andhiii079845]

Please post your used column defintion.

[andhiii079845]

My fault: try this:

test = datediff(if('calendar'[Date]<date(calendar[year],07,01),date(calendar[year]-1,07,01),date(calendar[year],07,01)),calendar[DATE],WEEK)

I forget that the correction if the year is not everytime needed. 

[andhiii079845]

Perhaps you have to ADD a +1:

[Clwin1012]

First... THANK YOU for helping !!!

closer, but not quite yet.

using either -1 or +1 it starts the year off at "0"

[andhiii079845]

You have to add it in the end:

test = datediff(if('calendar'[Date]<date(calendar[year],07,01),date(calendar[year]-1,07,01),date(calendar[year],07,01)),calendar[DATE],WEEK)+1

[Clwin1012]

That fixed week 23 in December but threw off Fiscal week 53 in June.  

Here is the final issue, week end 7/3/21 should be fiscal week 53

week end 7/10/21 should be fiscal week 1

thank you soooo much!!!

[andhiii079845]

Than you have to change the date in the datediff formula. ,07,01 in the formula should be ,07,08 everywhere: 

07: the month, 08: the day

test = datediff(if('calendar'[Date]<date(calendar[year],07,08),date(calendar[year]-1,07,08),date(calendar[year],07,08)),calendar[DATE],WEEK)+1

[Clwin1012]

Is there a way I can send you the table?  

new formula sets what should be week 1 to 53

[andhiii079845]

Are you able to summerize your desired result in a table, special in the changing of year and fiscal year? this will help a lot. like

31.06.2018 - 53

01.07.2018 - 1 

31.12.2018 - X 

[Clwin1012]

I am not sure I understand what you mean by summarize.  the end goal is to be able to do a comparison of current week this year to same week last year which will include a year over year dollar and % varaince. I was hoping to utilize calendar or fiscal week numbers to filter the page, but the week 53 threw me off.

again I am new to power bi, stuggling to learn DAX to build a dashboard based on what the business unit needs, which is comparisons by Day, week, month,  current to prior period and year over year.

I can always take the calendar and put it in excel, manually add the week numbers, but I thought power bi should be able to do this.

[andhiii079845]

Please write down for which date you want to see which weeknumber. I did not understand the problem. Please no screenshot, make a table. This make it easier for me.

[Clwin1012]

I have a table created, I don't see where in here I can attach it.

[andhiii079845]

You can use the table function of the forum or share it via dropbox / onedrive

[andhiii079845]

By shifting the week number in the fiscal year you have to calculated your weeknumber in a other way it think.

Only one idea:

datediff(date(RollingCalendar[year]-1,07,01),RollingCalendar[DATE],WEEK)