Creating a "BY WEEK" Date Table labeled like "Mar 13" With Weeks Being Consecutive

Hi Everyone,

I created this Date Table:

It gave me this:

The weeks recycle per year, meaning Total Order Amounts of 'Weeknum 4' will be 2013 and 2014 added together which will mess up my chart. Is there any way to seperate them by creating a date table by consective weeks without going back to "Week 1" at the start of a New Year?

ALSO: This is what my column chart looks like:

As you can see it's by week.

I need to make it so the X-Axis is labeled by "MMM-YYYY" with lapses in between like this:

I created a column for dates according to "MMM-YYYY" format but when I put it in it comes out all wonky, not even putting the dates in order:

Again, thank you so much for any help --- it would greatly, greatly appreciated.

Thanks,

Michael G.

Anonymous
2 years ago
Hi MichaelG1117 ,

Based on the information you provided, regarding your previous question, you can follow these steps:

1.Add a column.

Consecutive Week Number = WEEKNUM ( [Date], 2 ) + 52 * ( YEAR ( [Date] ) - YEAR ( MINX ( ALL ( 'Date Table' ), [Date] ) ) )

Final output:

For the X-axis labels, you can follow these steps:

Create a new column in the date table and change its type.

Month Year = FORMAT([Date], "MMM-YYYYY")

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}
Creating a Date Table.pbix60 KB

4 Replies

Anonymous
Not applicable
2 years ago
Hi MichaelG1117 ,

Based on the information you provided, regarding your previous question, you can follow these steps:

1.Add a column.

Consecutive Week Number = WEEKNUM ( [Date], 2 ) + 52 * ( YEAR ( [Date] ) - YEAR ( MINX ( ALL ( 'Date Table' ), [Date] ) ) )

Final output:

For the X-axis labels, you can follow these steps:

Create a new column in the date table and change its type.

Month Year = FORMAT([Date], "MMM-YYYYY")

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Creating a Date Table.pbix60 KB
- MichaelG1117
  Helper I
  2 years ago
  Hi Anonymous ,
  
  Is there a way to write that 'Consecutive Week Number' column directly into a table? I am not allowed to make calculated columns for this.
  
  I'm assuming that after making the initial table it would be something like:
  
  return
  ADDCOLUMNS(
  _table,
  "Total Order Amount",
  
  ..But I'm unsure about the correct syntax regarding your code above to create it in directly into the table.
  
  AS FOR THE COLUMN CHART:
  I created the Month Year Column, changed the Data Type to "Date" and the Format to "YYYY-MM" but mine does not look like yours.
  
  Each column in the chart also has to be "BY WEEK" (Weeknum), not by month, so the Week numbers are still on there also as I'm putting both "WEEKNUM" and "MONTH YEAR" into the X-Values.
  
  Even when I don't add "WeekNum" on the X-axis in there it still doesn't have the "Mar 2013" format:
  
  This is what I need it to look like:
  
  I'm assuming they created a Date Column that incorporates both WeekNumber AND Date?
  
  I did create two date columns that incorporate Week Number and Year:
  
  "Week Number & Year", "W" & WEEKNUM([Date]) & " " & YEAR([Date]),
  "WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] ),
  
  ...But when I try to change the Data Type to DATE it does not allow me to do so, I'm assuming because it's mixing integers and varchar.
  
  I guess I'm now wondering if there is a way to create a DATE COLUMN that incorporates the "Mar 2013" Format along with the WeekEnding Number to go with it.
  
  Again, thanks ---
  - Anonymous
    Not applicable
    2 years ago
    Hi MichaelG1117 ,
    
    According to a way to write that 'Consecutive Week Number' column directly into a table, you can add this Dax below:
    
    Date Table = VAR _Table= ADDCOLUMNS( CALENDAR(DATE(2013,1,24),DATE(2014,2,31)), "Consecutive Week Number",WEEKNUM([Date],2) + 52 * ( YEAR ( [Date] ) - YEAR (DATE(2013,1,24) ) ), "Week Number & Year","W" & WEEKNUM([Date]) & " " & YEAR([Date]), "WeekYearNumber",YEAR([Date]) & 100 + WEEKNUM([Date]), "MonthShortYear",FORMAT([Date],"mmm-YYYY") ) RETURN _Table
    
    Since the data on the x-axis in Clustered column chart is in alphabetical or numerical order, you should transform "Mar 2013" into "2013-3" to achieve your goal:
    
    Final output:
    
    How to Get Your Question Answered Quickly - Microsoft Fabric Community
    
    If it does not help, please provide more details with your desired out put and pbix file without privacy information.
    
    ^{Best Regards,}
    
    ^{Yifan Wang}
    
    ^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}
    Creating a Date Table.pbix67 KB