topic Re: Rolling Calendar - Week 53 and assigning a fiscal week number in DAX Commands and Tips

Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Mon, 20 Mar 2023 17:38:51 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 19:34:40 GMT

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)

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Mon, 20 Mar 2023 20:06:27 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:08:10 GMT

Please post your used column defintion.

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:14:20 GMT

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.

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:17:24 GMT

Perhaps you have to ADD a +1:

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Mon, 20 Mar 2023 20:25:21 GMT

First... THANK YOU for helping !!!

closer, but not quite yet.

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:30:40 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Mon, 20 Mar 2023 20:36:16 GMT

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!!!

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:43:15 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Mon, 20 Mar 2023 20:47:13 GMT

Is there a way I can send you the table?

new formula sets what should be week 1 to 53

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Mon, 20 Mar 2023 20:53:51 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Tue, 21 Mar 2023 12:11:10 GMT

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.

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

ppm1 — Tue, 21 Mar 2023 12:44:19 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Tue, 21 Mar 2023 15:18:11 GMT

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.

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Tue, 21 Mar 2023 15:41:22 GMT

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.

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

Clwin1012 — Tue, 21 Mar 2023 16:33:30 GMT

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

Re: Rolling Calendar - Week 53 and assigning a fiscal week number

andhiii079845 — Tue, 21 Mar 2023 17:33:39 GMT

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