Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Build calendar table with fixed weeknumbers assigned to months, quarters etc.

Hi all!

 

I need some help to figure out how I can a calender that follows the logic in the attached picture. 

 

We have sales cycles per week, so running on a standard ISO calender does not work when we are reporting. To compare previous years against this year we follow this logic:

 

week logic.jpgI need to build it with a date column as well, since i work with multiple data sources whic use date as the key. I struggle to find a way to build a calender that follows this logic automatically. 

 

Any help is greatly apreciatedt!!! 

 

Beste regards Christer

 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Seems that there are some issues in your if statement.

Like "if [WeekNb] >= 6 or [WeekNb] <=9 then 2", the logic should be "and" not "or", this is why everything else was tagged with 2.

Try to modify the formula like this:

= Table.AddColumn(
#"Changed Type", "NT-MonthNb", each 
if [WeekNb] >= 53 and [MonthNb] = 1 or [WeekNb] <= 5 then 1 else 
if [WeekNb] >= 6 and [WeekNb] <= 9 then 2 else 
if [WeekNb] >= 10 and [WeekNb] <= 13 then 3 else 
if [WeekNb] >= 14 and [WeekNb] <= 18 then 4 else 
if [WeekNb] >= 19 and [WeekNb] <= 22 then 5 else 
if [WeekNb] >= 23 and [WeekNb] <= 26 then 6 else 
if [WeekNb] >= 27 and [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 and [WeekNb] <= 35 then 8 else  
if [WeekNb] >= 36 and [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 and [WeekNb] <= 44 then 10 else  
if [WeekNb] >= 45 and [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 and [WeekNb] <= 53 or [MonthNb] < 1 then 12 else null,
Int64.Type)

1.png2.png

Attached a sample file in the below, hopes it could help.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Jimmy801
Community Champion
Community Champion

Hello @Anonymous 

 

when the financial year is starting? What you have to do is to use the date of every row, detracting days, or month (the delay of your financial year) and then making the calculation again using the changed date as input.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Anonymous
Not applicable

Hi @Jimmy801 

 

Our financial year follows the ISO calandar and starts at January 1st.  So in 2021 the first days of the year is week 53. The problem is not on week level, beacause week 1 is alway compared to week 1, no mattere the difference in dates. 

 

The challenge occours when I look at the data per month, quarter or tertial. January is always week 1-5, even if week five is in February on the ISO calendar. I tried to use a nested IF statement but this only gives the correct week numbers for January, everything else was tagged with 2. Not sure why though. 

 

#"Added Calendar NT-MonthNb" = Table.AddColumn(#"Added MonthYear", "NT-MonthNb", each
if [WeekNb] >=53 and [MonthNb] = 1 or [WeekNb] <=5 then 1 else
if [WeekNb] >= 6 or [WeekNb] <=9 then 2 else
if [WeekNb] >= 10 or [WeekNb] <= 13 then 3 else
if [WeekNb] >= 14 or [WeekNb] <= 18 then 4 else
if [WeekNb] >= 19 or [WeekNb] <= 22 then 5 else
if [WeekNb] >= 23 or [WeekNb] <= 26 then 6 else
if [WeekNb] >= 27 or [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 or [WeekNb] <= 35 then 8 else
if [WeekNb] >= 36 or [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 or [WeekNb] <= 44 then 10 else
if [WeekNb] >= 45 or [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 or [WeekNb] <= 53 and [MonthNb] < 1 then 12 else null, Int64.Type),

Hi @Anonymous ,

Seems that there are some issues in your if statement.

Like "if [WeekNb] >= 6 or [WeekNb] <=9 then 2", the logic should be "and" not "or", this is why everything else was tagged with 2.

Try to modify the formula like this:

= Table.AddColumn(
#"Changed Type", "NT-MonthNb", each 
if [WeekNb] >= 53 and [MonthNb] = 1 or [WeekNb] <= 5 then 1 else 
if [WeekNb] >= 6 and [WeekNb] <= 9 then 2 else 
if [WeekNb] >= 10 and [WeekNb] <= 13 then 3 else 
if [WeekNb] >= 14 and [WeekNb] <= 18 then 4 else 
if [WeekNb] >= 19 and [WeekNb] <= 22 then 5 else 
if [WeekNb] >= 23 and [WeekNb] <= 26 then 6 else 
if [WeekNb] >= 27 and [WeekNb] <= 31 then 7 else
if [WeekNb] >= 32 and [WeekNb] <= 35 then 8 else  
if [WeekNb] >= 36 and [WeekNb] <= 39 then 9 else
if [WeekNb] >= 40 and [WeekNb] <= 44 then 10 else  
if [WeekNb] >= 45 and [WeekNb] <= 48 then 11 else
if [WeekNb] >= 49 and [WeekNb] <= 53 or [MonthNb] < 1 then 12 else null,
Int64.Type)

1.png2.png

Attached a sample file in the below, hopes it could help.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @Anonymous 

 

I can still not follow you. Can you make a practical example and describe it. From both of your screenshots I'm not able to get any logic. How can march only have 3 weeks? on witch date you have witch week? Not clear to me, sorry


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

CNENFRNL
Community Champion
Community Champion

@Anonymous , there are some out-of-the-box functions in this regard,

let
    Source = Table.FromList(List.Dates(#date(2021,1,1),365,#duration(1,0,0,0)), Splitter.SplitByNothing(), {"Date"}),
    #"Added Calculation" = Table.AddColumn(Source, "Calculation", each [Month = Date.ToText([Date], "MMMM"), Week = Date.WeekOfYear([Date]), Quarter = Date.QuarterOfYear([Date]), Tertial = Number.RoundUp(Date.Month([Date])/4)]),
    #"Expanded Month" = Table.ExpandRecordColumn(#"Added Calculation", "Calculation", {"Month", "Week", "Quarter", "Tertial"}, {"Month", "Week", "Quarter", "Tertial"})
in
    #"Expanded Month"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

Hi @CNENFRNL 

 

This did not give quite the result I was hoping for. Let me put it in context. 

 

I have a regular calendar table following the ISO standard. I need to add columns for company months, quarters and tertials based on the above structure. So week 5 on the ISO calendar is alway in January, even tho all the dates are in February. 

 

I've added a picture file to show the setup. I have a dynamic date table, I wish to add these columns to it. And I do not know how to write that code. 

 

Christer

ChristerMTvedt_0-1616149348504.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors