Limit x-axis

[Anonymous]

I have a line chart - it is filtered (filters on this page) by date column. Filter on the page is set to relative date, and show items when the value is in the last 7 days. 

The chart is showing only data for the last 7 days, however, it's showing the rest of the days as 0 (May 1st, 2022 - May 9th, 2022 & May 17th - May 31st, 2022); which is incorrect:

1) There are data on May 1st 2022 - May 9th 2022 

2) May 17th - 31st is in the future. 

Is there a way to show only the 7 days on the chart? 

Thank you

[Anonymous · Accepted solution]

For future viewers and reference, it looks like I have a measure with IF (ISBLANK) to account for one of the categories to show 0 and not <BLANK>. So the chart without any data for the rest of the dates, showed 0. Removing that will just show the 7 days chart in x-axis. 

Not really a fix, but, sort of the cause of the problem and the workaround. 

[JoaoMarcelino]

Hi @Anonymous ,

I'm not sure if I understood correctly, but it seems you are not using a "proper" Calendar dimension; if you are using the date column from the facts table, I would recommend you to create a date table (the reason why you should use a date table or date calendar is fully explained in this article by the brilliant Reza Rad: Do You Need a Date Dimension? - RADACAD).

That being said, there are several ways to create a date table you can; my favorite is (keep in mind this code is not mine but I am unsure to who I should give kudos, but it is always used at the company I work - Devscope). If you know how to do it, jump to step 7 or after 🙂
Anyway, here it goes:
1- Copy this code (the 3rd step allows you to switch between "en- US" or "pt-PT" if you need to have it in Portuguese for some reason 🙂 ) :

let

  P_Today = DateTime.LocalNow(),

  P_StartDate = #date(2018, 1, 1),

  P_EndDate = #date(Date.Year(P_Today),12,31),

  P_Culture = "en-EN",

  P_FirstDayOfWeek = 1,

  P_IsCarnivalHoliday = true,

  Holiday = if P_Culture = "pt-PT" then "Feriado" else "Holiday",

  Quarter = if P_Culture = "pt-PT" then "T" else "Q",

  Week = if P_Culture = "pt-PT" then "S" else "W",

  Weekend = if P_Culture = "pt-PT" then "Fim de Semana" else "Weekend",

  WorkDay = if P_Culture = "pt-PT" then "Dia Útil" else "Work Day",

  DayCount = Duration.Days(Duration.From(P_EndDate - P_StartDate)) + 1,

  Source = List.Dates(P_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"}}),

  InsertId = Table.AddColumn(RenamedColumns, "DateId", each Date.Year([Date])*10000 + Date.Month([Date])*100 +Date.Day([Date])),

  InsertYear = Table.AddColumn(InsertId, "Year", each Date.Year([Date])),

  InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),

  InsertSemester = Table.AddColumn(InsertQuarter, "Semester", each if [Quarter] < 3 then 1 else 2),

  InsertMonth = Table.AddColumn(InsertSemester, "Month (#)", each Date.Month([Date])),

  InsertWeek = Table.AddColumn(InsertMonth, "Week", each Date.WeekOfYear([Date], P_FirstDayOfWeek)),

  InsertDay = Table.AddColumn(InsertWeek, "Day", each Date.Day([Date])),

  InsertMonthName = Table.AddColumn(InsertDay, "Month (Long)", each Date.ToText([Date], "MMMM", P_Culture), type text),

  InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month", each try(Text.Range([#"Month (Long)"],0,3)) otherwise [#"Month (Long)"]),

  InsertCalendarWeek = Table.AddColumn(InsertShortMonthName, "Week (Year)", each Week & Number.ToText([Week]) & " " & Number.ToText([Year])),

  InsertCalendarMonth = Table.AddColumn(InsertCalendarWeek, "Month (Year)", each [#"Month"] & " " & Number.ToText([Year])),

  InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter (Year)", each Quarter & Number.ToText([Quarter]) & " " & Number.ToText([Year])),

  InsertCalendarSem = Table.AddColumn(InsertCalendarQtr, "Semester (Year)", each "S" & Number.ToText([Semester]) & " " & Number.ToText([Year])),

  InsertDayWeek = Table.AddColumn(InsertCalendarSem, "Week Day (#)", each Date.DayOfWeek([Date], P_FirstDayOfWeek ) + 1),

  InsertDayName = Table.AddColumn(InsertDayWeek, "Week Day", each Date.ToText([Date], "dddd", P_Culture), type text),

  InsertWeekYear = Table.AddColumn(InsertDayName, "WeekYearId", each [Year] * 100 + [Week]),

  InsertMonthYear = Table.AddColumn(InsertWeekYear, "MonthYearId", each [Year] *100 + [#"Month (#)"]),

  // InsertStartWeek = Table.AddColumn(InsertWeekYear , "Start of Week", each Date.StartOfWeek([Date], P_FirstDayOfWeek), type date),

  // InsertEndWeek = Table.AddColumn(InsertStartWeek , "End of Week", each Date.EndOfWeek([Date], P_FirstDayOfWeek), type date),

  InsertQuarterYear = Table.AddColumn(InsertMonthYear, "QuarterYearId", each [Year] * 100 + [Quarter]),

  InsertSemesterYear = Table.AddColumn(InsertQuarterYear, "SemesterYearId", each [Year] * 100 + [Semester]),

  #"Capitalized Each Word" = Table.TransformColumns(InsertSemesterYear,{{"Month (Long)", Text.Proper}, {"Month", Text.Proper}, {"Month (Year)", Text.Proper}, {"Week Day", Text.Proper}}),

  #"Relative (Year)" = Table.AddColumn(#"Capitalized Each Word", "Year (Relative)", each [Year] - Date.Year(P_Today)),

  #"Relative (Month)" = Table.AddColumn(#"Relative (Year)", "Month (Relative)", each [#"Year (Relative)"] * 12 + ([#"Month (#)"] - Date.Month(P_Today))),

  #"Relative (Week)" = Table.AddColumn(#"Relative (Month)", "Week (Relative)", each Duration.TotalDays(DateTime.Date(Date.StartOfWeek([Date])) - DateTime.Date(Date.StartOfWeek(P_Today))) / 7),

  #"Relative (Day)" = Table.AddColumn(#"Relative (Week)", "Day (Relative)", each Duration.TotalDays([Date] - DateTime.Date(P_Today))),

  // MergedHolidays = Table.NestedJoin(#"Relative (Day)",{"Date"},GetHoliday(P_StartDate,P_EndDate,P_Culture, P_IsCarnivalHoliday),{"Date"},"Holidays",JoinKind.LeftOuter),

  // ExpandedHolidays = Table.ExpandTableColumn(MergedHolidays, "Holidays", {"Holiday"}, {"Holiday"}),

  // AddedWorkDay = Table.AddColumn(ExpandedHolidays, "Work Day", each if [Holiday] = null then (if [#"Week Day (#)"] > 5 then Weekend else WorkDay) else Holiday),

  #"Reordered Columns" = Table.ReorderColumns(#"Relative (Day)", {"Date", "Day", "Week Day (#)", "Week Day", "Week", "Month (Long)", "Month", "Month (#)", "Quarter", "Semester", "Year", "Week (Year)", "Month (Year)", "Quarter (Year)", "Semester (Year)", "WeekYearId", "MonthYearId", "QuarterYearId", "SemesterYearId", "Day (Relative)", "Week (Relative)", "Month (Relative)", "Year (Relative)"}),

  #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns", {{"Day", Int64.Type}, {"Week Day (#)", Int64.Type}, {"Week", Int64.Type}, {"Month (#)", Int64.Type}, {"Quarter", Int64.Type}, {"Semester", Int64.Type}, {"Year", Int64.Type}, {"Week (Year)", type text}, {"Quarter (Year)", type text}, {"Semester (Year)", type text}, {"WeekYearId", Int64.Type}, {"SemesterYearId", Int64.Type}, {"MonthYearId", Int64.Type}, {"QuarterYearId", Int64.Type}, {"Day (Relative)", Int64.Type}, {"Month (Relative)", Int64.Type}, {"Year (Relative)", Int64.Type}, {"DateId", Int64.Type}, {"Week (Relative)", Int64.Type}}),

  ColumnPT = Table.RenameColumns(#"Changed Type", {{"Date", "Data"}, {"DateId", "DataId"}, {"Day", "Dia"}, {"Week Day (#)", "Dia Semana (#)"}, {"Week Day", "Dia Semana"}, {"Week", "Semana"}, {"Month (Long)", "Mês (Extenso)"}, {"Month", "Mês"}, {"Month (#)", "Mês (#)"}, {"Quarter", "Trimestre"}, {"Semester", "Semestre"}, {"Year", "Ano"}, {"Week (Year)", "Semana (Ano)"}, {"Month (Year)", "Mês (Ano)"}, {"Quarter (Year)", "Trimestre (Ano)"}, {"Semester (Year)", "Semestre (Ano)"}, {"WeekYearId", "SemanaAnoId"}, {"MonthYearId", "MesAnoId"}, {"QuarterYearId", "TrimestreAnoId"}, {"SemesterYearId", "SemestreAnoId"}, {"Day (Relative)", "Dia (Relativo)"}, {"Month (Relative)", "Mês (Relativo)"}, {"Year (Relative)", "Ano (Relativo)"}, {"Week (Relative)", "Semana (Relativa)"}}),

  result = if P_Culture = "pt-PT" then ColumnPT else #"Changed Type"

in

  result

2- Open Power Query:

3- Create a new blank query:

4- Click on Advanced Editor -> delete the piece of code there (inside the red "circle"):

5 - Paste the previously copied code from step 1 and change the steps P_StartDate and P_EndDate accordingly (i.e., if you want the start date to be "2020, 1, 1" replace the year 2018 by 2020; if you want the end date to be the 31st of December of the current year you may let it be like this, if you want the end date of calendar to be the previous year, you just need to insert a "-1" after Date.Year(P_Today)-1, 12, 31).

6- Click "Done" below, Rename the query to "Calendar" and then "close and apply":

7- Create a relationship between table Calendar and your facts table through "date" field.

8- Either:
-Drag and drop "Day (relative)" to page filters and choose the current (0) and the last 7 (up to -7):

Or
- Drag and drop the filed "date" (from table dimension) to Page filter on filter pane and by choosing "relative" -> in the last 7 days.

I know this was too long (sorry for that) but I believe it can also serve as answer/tutorial to many others that might need help with this subject 🙂

Hope I was of assistance!
Cheers
Joao Marcelino
Ps- Did I answer your question? Mark my post as a solution! Kudos are also appreciated 🙂