calendar showing data

Question

Short Sample Calandar Table, with 4 fields

Start Date--Step Number--Task Name--Duration (in days)

1/2/22------5---------------Midway------22

3/1/22------7---------------Final Stages--11

5/12/22-----10--------------Last Step-----12

I have been trying out the different calendars and have these questions:

1. I need a six month to one year calendar, it it possible to make one with DAX or M code or should I use a pre made calendar?

2. Is it possible to show more than one parameter, such as with printed text, placed in the start date cell on three different lines, perhaps as:

=============

1/22

----------

Step: 5

Midway

22 days

=============

Basically, I am wondering if I can take control of (a) the process of extracting data from a calendar table by use DAX or M code, and of the (b) the process of creating a calendar, and (c) if I want to use the GUI of Power BI, can I create a calendar that will accept multiple parameters since most of the premade calendars seem to accept two parameters: perhaps date and one other value.

Thank you,

Steve

se2 · Answer

https://www.youtube.com/watch?v=smcYYMsZcco&t=487s explains how to get a one month calendar, I still wonder how to make it return a one year calender? But here is the M-code for a one month calendar:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Column1]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Start of Month]), type date),
#"Inserted Day" = Table.AddColumn(#"Inserted End of Month", "Day", each Date.Day([End of Month]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Day", "date", each List.Dates([Start of Month],[Day],#duration(1,0,0,0))),
#"Expanded date" = Table.ExpandListColumn(#"Added Custom", "date"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}}),
#"Inserted Day1" = Table.AddColumn(#"Changed Type1", "Day.1", each Date.Day([date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day1", "Day Name", each Date.DayOfWeekName([date]), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Day Name", {{"Day Name", each Text.Start(_, 3), type text}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each ([Day Name] = "Sun")),
#"Day 1" = #"Filtered Rows"{0}[Day.1],
Custom1 = #"Extracted First Characters",
#"Inserted Integer-Division" = Table.AddColumn(Custom1, "Integer-Division", each Number.IntegerDivide([Day.1]+(7-#"Day 1"), 7), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Column1",

"Start of Month", "End of Month", "Day", "date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Day Name"]), "Day Name", "Day.1"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Integer-Division"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"})
in
#"Reordered Columns"

se2 · Answer

Put this range from excel in power query, I now realize that I should have preserved dates as in 1/2/21, in the above; and the date in the below table should be formated as a Date note Date + Time. The next step might be to do a join on the date fields, from both tables?

Start Date-----Step Number--Task Name--Duration

1/1/2022 12:00:00 AM	5	Midway	5
2/5/2022 12:00:00 AM	8	Near End	6
3/12/2022 12:00:00 AM	9	Final Step	10

se2 · Answer

Calendar with full datesSun----------Mon-------Tue---------Wed--------Thu---------Fri-----------SatThe following produces the above with dates that can be used as the common fieldletSource = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),#"Inserted Start of Month" = Table.AddColumn(#"Changed Type", "Start of Month", each Date.StartOfMonth([Column1]), type date),#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Start of Month]), type date),#"Inserted Day" = Table.AddColumn(#"Inserted End of Month", "Day", each Date.Day([End of Month]), Int64.Type),#"Added Custom" = Table.AddColumn(#"Inserted Day", "date", each List.Dates([Start of Month],[Day],#duration(1,0,0,0))),#"Expanded date" = Table.ExpandListColumn(#"Added Custom", "date"),#"Changed Type1" = Table.TransformColumnTypes(#"Expanded date",{{"date", type date}}),#"Inserted Day1" = Table.AddColumn(#"Changed Type1", "Day.1", each Date.Day([date]), Int64.Type),#"Inserted Day Name" = Table.AddColumn(#"Inserted Day1", "Day Name", each Date.DayOfWeekName([date]), type text),#"Extracted First Characters" = Table.TransformColumns(#"Inserted Day Name", {{"Day Name", each Text.Start(_, 3), type text}}),#"Filtered Rows" = Table.SelectRows(#"Extracted First Characters", each ([Day Name] = "Sun")),#"Day 1" = #"Filtered Rows"{0}[Day.1],Custom1 = #"Extracted First Characters",#"Inserted Integer-Division" = Table.AddColumn(Custom1, "Integer-Division", each Number.IntegerDivide([Day.1]+(7-#"Day 1"), 7), Int64.Type),#"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Column1", "Start of Month", "End of Month", "Day"}),#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"date", "Day.1", "Day Name", "Integer-Division"}),#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Day.1"}),#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Day Name"]), "Day Name", "date"),#"Reordered Columns1" = Table.ReorderColumns(#"Pivoted Column",{"Integer-Division", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}),#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns1",{"Integer-Division"})in#"Removed Columns2"&nbsp;

se2 · Answer

The above produces a table of dates in this format:S&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; W&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; T &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; S1/1/21 1/2/21 1/3/21 1/4/21 1/5/21 1/6/21 1/7/21&nbsp;The question remains, can power bi be used to iteratively check for matches between a table in the below format with one in the&nbsp; above format?&nbsp;Start Date&nbsp;&nbsp;&nbsp; Step Number&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Task Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Duration1/1/21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Midway&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5

se2 · Answer

1.a. I have not figured out if power bi can iterate through cells, probably with python," but it can do this:1.b. join the calendar project table -- that is the table that has a date column with the table that has the date for a full calendar -- do a merge using the common date columns.1.c. In the new merge table, create a 'date plus' column, with the date as text, inserting carriage returns &amp;"#(cr)"&amp; as needed, and other infor taken from other columns, as needed.2. Remove unneeded columns, move the 'date plus" column to the far left, and then pivot3.&nbsp; The result will look something like this.&nbsp; (I am having trouble figuring out how to paste an excel table in)4. So there is at least one way for power bi to join and pivot a calendar project table with a table with all calendar dates.&nbsp;S M T W T F S1/1/21 1/2/21 1/3/21 1/4/21 1/5/21 1/6/21 1/7/21midway&nbsp;letSource = Table.NestedJoin(#"calendar with full dates (2)", {"date - Copy"}, Table4, {"Start Date"}, "Table4", JoinKind.LeftOuter),#"Expanded Table4" = Table.ExpandTableColumn(Source, "Table4", {"Start Date", "Step Number", "Task Name", "Duration"}, {"Table4.Start Date", "Table4.Step Number", "Table4.Task Name", "Table4.Duration"}),#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table4",{{"date - Copy", type text}}),#"Added Custom" = Table.AddColumn(#"Changed Type", "date plus", each if [Table4.Task Name] &lt;&gt; nullthen [#"date - Copy"] &amp; "#(cr)" &amp; [Table4.Task Name]else [#"date - Copy"]),#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"date - Copy", "Table4.Start Date", "Table4.Step Number", "Table4.Task Name", "Table4.Duration"}),#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"date plus", "Day Name", "Integer-Division"}),#"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[#"Day Name"]), "Day Name", "date plus")in#"Pivoted Column"

Forum Discussion

calendar showing data

6 Replies

Related content

ERROR: Calendar Visual by MAQ Software showing error message

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar

Show last calendar month of data