Data Table weekly measures

[jorgeutd]

Hello Sam,

I hope you are doing well. I use a lot of the weekly Dax measures that I learned here, one particular problem that I currently have is that when I am doing reporting, for example, the sum of weekly filed claims or paid claims, we want to be able to report from Monday to Friday. Using the date code table from here is that the week goes from Sunday to Saturday.

I tried to change the week ending field from the data table that I created using a similar code to yours but I did not work it.

My start date is 01-01-2017 and my end date is 12-31-2018.

Please let me know your recommendations on this issue.

The code that I am using is:

llet fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date],0),type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date],0)),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
in

[sam.mckay]

I don’t feel you need to change the code here.

You just need to create a calculated column which differentiates between a weekend or weekday.

Then you can created a simple formula using CALCULATE and FILTER to isolate the weekday sales.

Something like this

This should do it. Doesn’t need to be anymore complicated than this.

[jorgeutd]

Hi Sam,

Thanks for the quick response. This work if I want to create a subset of the measure going from Monday to Saturday right, I want to be able to present a range of measure from Monday to Sunday and separate them by week 1, week 2, etc.

See the attachment.

[jorgeutd]

That ending day should not be saturday.

[sam.mckay]

Ok maybe I’m not really getting what you need here.

As a quick fix is there any reason why you couldn’t just do this to the date table once it’s created…

Change the Sunday ( 0 ) to ( 7 ) - like the below

Just a simple replace values

[jorgeutd]

Hi Sam,

Let me give a better explanation of what I am trying to do, I am trying to report of the weekly total from Monday to Sunday and then compare the difference from the previous week, and putting in table all the weekly total for 2018 for example and at the same time show the week results that we are interested in, below this tables I have the vertical bar showing the positive differences in one side and negative in the left side. I am not sure why the build in code in the date tables is showing that my week goes from Sunday to Saturday, Sunday being the day 0 and Saturday being the 6 so when I am trying to display this results in a table grab the measures from Sunday to Saturday and keep showing week ending day as a Saturday. I am attaching my report page for a better understanding. I change my monday to be the day 0 in the date code but it keep putting that my week 1 for example in 2017 ends on 1/07/2017 and my week to ends on 1/14/2017 and so on.

[sam.mckay]

I’ll look into this further. Can’t find a solution at the present time.

[sam.mckay]

Just found a solution.

Sub this line of code into the correct place and it will adjust the weeks correctly for what you need

InsertWeekNumber = Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear(Date.AddDays([Date],-1))),

[Paul]

Hi Sam,
Nice!
Now to add a problem I come across using this table.
Day of week will show as Monday=0 and Sunday=6. I managed to amend the corresponding line in the query to now show Monday=1. However, Sunday is now 0 and I would like to show 7. Can this be done here as well?

Further to weeknumber, check the date 1st. Jan 2016, this will show in week 1, where it is actually in week 53 of 2015.
The weeknum DAX function in Power BI allows to add 21 (just like in Excel, not documented in DAX) to show the correct (ISO) week, is this possible in this query too?
Paul

[jorgeutd]

Hi Sam,

First, thank you for taking the time for helping me out with this problem.

I did the changes that you recommended, and my week count is good, but everything else is no working the way we thought. My week ending day keep being Saturday even though that we changed for the week start on Monday, and Paul is right about the showing the first day of the year showing as in the week number 53 of the year before.

I am attaching pictures on this response so you can see the behaviors.

Thanks Sam.

[sam.mckay]

Ok give this code a go

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek(Date.AddDays([Date],-1))),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek(Date.AddDays([Date],-1)), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear(Date.AddDays([Date],-1))),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
    AddFY
in
    fnDateTable

[sam.mckay]

This code adjust the weekending, and dayofweek numbers.

This is what it looks like now.

Hopefully this cover what you need.

Let me know if there’s anything else.

Thanks

[jorgeutd]

Good morning Sam,

I appreciate a lot of you taking the time to answer this questions coming from all the places.

This is Definitely an improvement, now the days go in the proper order, nevertheless, I keep having the same issues that I mentioned before and that Paul commented too.

First, the week ending keep being a Saturday on the table and on top of this the first day of 2018 shows as it belongs to week 53 of the year before.

Please find attached the images.

I wonder if there is some kind of prebuilt rule on power bi that does not allow to show the week ending day as Sunday or there is something more that we should be changing in the date table.

Thanks,

Sam.

[jorgeutd]

[sam.mckay]

There certainly is a Power BI rules that follow this sort of logic.

To change the week ending, I would honestly create a calculated column for this as this solved it very easily.

To fix the week 53, I can’t find a solution right now. But any reason you couldn’t turn this into a 1 by replacing values? That looks like it would work here.

Let me know.

Sam