Creating A Line Chart To Reflect No of Items in a Location by Month

[Syndicate_Admin]

Hello,

I would like to create a line chart that reflects how many items are at a certain location in a particular month.

The x-axis will be month, and the y-axis is the number of items. 

The table below is an example of data I have for a particular item.

Item	Date	From	To
XXXX1	Wednesday, 20 February 2019	Home	Part Time Job
XXXX1	Friday, 12 April 2019	Part Time Job	Jobless
XXXX1	Wednesday, 15 May 2019	Jobless	University
XXXX1	Friday, 20 December 2019	University	Part Time Job
XXXX1	Thursday, 8 October 2020	Part Time Job	High School
XXXX1	Thursday, 7 September 2023	High School	Part Time Job
XXXX1	Monday, 11 September 2023	Part Time Job	College Prep
XXXX1	Friday, 27 October 2023	College Prep	University
XXXX1	Thursday, 22 February 2024	University	Work

You can see the item was at University from 27 October 2023 to 22 February 2024. 

At the moment, the line graph only shows how many items entered a location in a month, and item XXXX1 will only appear to be in University in the month of October 2023 because that is the date when it moved to University.

However, I want it to appear in University also for the months of Nov 2023, Dec 2023, Jan 2024 and Feb 2024, because it was in University for those months. In the line graphs below, I have filtered "To" to "University". 10 items entered "University" on 27 October 2023, and another item entered "University" on 12 Dec 2023. However, there should be 11 items altogether in Dec because the 10 items that entered University in October are still there (No items entered in November).

Is this possible?

[Syndicate_Admin]

Hi PBOBOP

You need to tranform your data into a proper fact with Power Query / Transform Data, so that you have one row for each item per day, detailing the current location of the Item.

Try using the M-Code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJRa8IwEMe/SshzHkxU3B7HpDhBJtjhoPQhrYcNS3vlGgf99ouWbLVrDSG54/K7+9+RJOFchJ2KhL85KL291g78FRFevRhvsU+/pHePcKqgOelWMDVjEWR00dR6Wz776AbLK7rX5FhsSmBbzO7wiMyNlYq91GRsAO8Jwf1poWmmSssl2+nfquGx4B+V+QZqjGtHq3rFa8ihzIAC2yMe6Y6LC3Wln9h77rDLoGYj0jfmXLBDXiDaiRQrdoDaBRlqPoAe6dhh1Q1A/s8xFPKK1sIZ2J6gHh/Hqt/LfIhMjvOvE6X6X0AthgM9In3xNP0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Item", type text}, {"Date", type date}, {"From", type text}, {"To", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Date", "Start Date of Current Location"}, {"From", "Previous Location"}, {"To", "Current Location"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "End Date of Current Location", each try #"Added Index" [Start Date of Current Location] {[Index]} otherwise null, Date.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each try List.Dates([Start Date of Current Location], Duration.Days([End Date of Current Location] -[Start Date of Current Location]) +1 ,#duration(1,0,0,0) ) otherwise null),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Custom", "Date"}})
in
    #"Renamed Columns1"

First the I create a new column with an Index starting from 1.

Then I add a new column where I get the Date value of the next row, in order to get Start and End Date of the "To" location on one row.

Then I Create a List of Dates between the Start Date and End Date in a new column, and expand that List to Rows, to get one row for each day:

You can use this table to then count the number of items in each location on each day.

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[Syndicate_Admin]

Hi @PBOBOP ,

While @Jonvoge 's solution will make getting daily counts very easy, it will also make your data VERY big VERY quickly.

I would recommend making some small transformations to get the 'To' date on each row like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFfC4IwFMW/ysVnH9wqrMcoIgJJyCiIHqZecjCdXFfgt28mlvZvT4Od37nn3J1OztEe5rjOAdMCq1TULnAPVhjTVVBt72xmX9c6R+fsvuQrkg8t4zAvSapOGAoyEMkcYaPjAdEbwCYQiKe3FSqsqq/2NsoSE8xjpE6+L+QNqZKmHhBRdqXWfQrbxOiW4N7fTC/Ihx2WphvER01neclgl2RaqwEU6KJtwT6Z36Oejfx+vIZZaKXwghASlj/Scd7/ET5+W8P5Dg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, From = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    sortItemDate = Table.Sort(chgTypes,{{"Item", Order.Ascending}, {"Date", Order.Ascending}}),
    addIndex1 = Table.AddIndexColumn(sortItemDate, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeItemIndex = Table.NestedJoin(addIndex0, {"Item", "Index1"}, addIndex0, {"Item", "Index0"}, "addIndex0", JoinKind.LeftOuter),
    expandDateTo = Table.ExpandTableColumn(mergeItemIndex, "addIndex0", {"Date"}, {"DateTo"}),
    remOthCols = Table.SelectColumns(expandDateTo,{"Item", "From", "Date", "DateTo"})
in
    remOthCols

Then use a measure to get the count of items on any given day, like this:

_itemsInLocationOverTime = 
VAR __cDate = MAX(dimCalendar[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT(yourTable[Item]),
    FILTER(
        yourTable,
        __cDate >= yourTable[Date]
        && ( __cDate <= yourTable[DateTo] || ISBLANK(yourTable[DateTo]) )
    )
)

This assumes you have an UNRELATED calendar table (dimCalendar), and you must use a dimCalendar field on the visual axis along with this measure.

Pete

[Syndicate_Admin]

Hi @Jonvoge and @BA_Pete ,

Thank you so much for your suggestions. I have tried both your methods but there is a problem which is my fault because I should have provided more data. Sorry about that. I have added a few more rows to give better context.

There are many items in this data set. So if we use the index method to create an end date for the period that the item was in a certain location, the End Date for the last location of item XXXX1 will be drawn from the Start Date of the first location of item XXXX2 (orange font below), and the End Date for the last location for XXXX2 will be drawn from the Start Date of the first location of item XXXX3 etc.

However, all the items will be sitting in their latest location today. (XXXX1 is still at Work, XXXX2 is at Home, and XXXX3 is still at Work). 

Is it possible to have the end date for the last row for each item to be today's date?

Item	Start Date	From	To	Index from 1	Index from 0	End Date
XXXX1	20 February 2019	Home	Part Time Job	1	0	12 April 2019
XXXX1	12 April 2019	Part Time Job	Jobless	2	1	15 May 2019
XXXX1	15 May 2019	Jobless	University	3	2	20 December 2019
XXXX1	20 December 2019	University	Part Time Job	4	3	08 October 2020
XXXX1	8 October 2020	Part Time Job	High School	5	4	07 September 2023
XXXX1	7 September 2023	High School	Part Time Job	6	5	11 September 2023
XXXX1	11 September 2023	Part Time Job	College Prep	7	6	27 October 2023
XXXX1	27 October 2023	College Prep	University	8	7	22 February 2024
XXXX1	22 February 2024	University	Work	9	8	06 May 2019
XXXX2	06 May 2019	Home	Work	10	9	20 January 2021
XXXX2	20 January 2021	Work	Home	11	10	15 March 2019
XXXX3	15 March 2019	Home	Part Time Job	12	11	22 May 2019
XXXX3	22 May 2019	Part Time Job	Work	13	12

[Syndicate_Admin]

Hi @BA_Pete,

Thank you for your suggestion, 

I am stuck at the mergeItemIndex step, when I merge it, all the numbers in Index1 are there. However after I "expandDateTo", the last line disappears as there is no matching number from Index0, instead of returning "null" like what you have in your table above, and items 109789 and 109790 completely disappear because they only have 1 line in Index1

mergeItemIndex: 

expandDateTo:

Do you know how I can get "null" to return for the lines with no match for Index1? 

[Syndicate_Admin]

Have you checked that these rows haven't just been re-sorted to the end of the table or something?

There's no reason why a Left Outer merge would remove any rows - that's the point of a Left Outer - but merges in PQ do very often change the sort order of your table, so maybe all the rows with null [DateTo] values have been dumped to the bottom of your table. Try filtering the post-merge query on [DateTo] = null to see if the rows exist.

If these rows do indeed appear to be missing, then you may need to send over some copyable sample data so I can try to reproduce the issue.

Pete

[Syndicate_Admin]

Hi @PBOBOP, regarding this @BA_Pete query. I don't know how many lines do you have in your database, but this one should be faster if you have a lot of lines.

let
    FnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text) as table =>
    //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca)
    let
        a = Table.Column(tbl, col),
        b = if shift = 0 or shift = null then a else if shift > 0
            then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
            else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
        c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
              ( if newColName <> null then {newColName} else
                if shift = 0 then {col & "_Duplicate"} else
                if shift > 0 then {col & "_NextValue"} 
                else              {col & "_PrevValue"} ))
    in
        c,
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFfC4IwFMW/ysVnH9wqrMcoIgJJyCiIHqZecjCdXFfgt28mlvZvT4Od37nn3J1OztEe5rjOAdMCq1TULnAPVhjTVVBt72xmX9c6R+fsvuQrkg8t4zAvSapOGAoyEMkcYaPjAdEbwCYQiKe3FSqsqq/2NsoSE8xjpE6+L+QNqZKmHhBRdqXWfQrbxOiW4N7fTC/Ihx2WphvER01neclgl2RaqwEU6KJtwT6Z36Oejfx+vIZZaKXwghASlj/Scd7/ET5+W8P5Dg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, From = _t]),
    ChangedType1 = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    GroupedRowsWithAddDateTo = Table.Group(ChangedType1, {"Item"}, {{"All", each FnShift(_, "Date", -1, "Date To") , type table}}),
    Combined = Table.Combine(GroupedRowsWithAddDateTo[All]),
    ChangedType2 = Table.TransformColumnTypes(Combined,{{"Date To", type date}})
in
    ChangedType2