Display line graph for two data from one table

[upniwesh]

I have one Excel sheet from where I am preparing the Dashboard.

I have data like below 

Task Name	Target Date	Alert Date
Task 1	13/06/2023	13/09/2023
Task 2	05/05/2023	10/08/2023
Task 3	10/05/2023	10/07/2023

Now I need to prepare a line chart month wise which should have two series one for Targetdate Count and another for Alert Date Count. We need to get in which month how many alerts are there and how many targets are there.

e.g. As per above table 

May Target - 2, Alert 0

JuneTarget 1, Alert 0

July Target 0, Alert 1

Aug Target 0, Alert 1

Sep Target 0, Alert 1

Oct Target 0, Alert 0

Thanks for the help.

Upniwesh

[slorin]

Hi, 

Another solution without Table.Join

let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Task Name"}, "Attribute", "Month"),
StartOfMonth = Table.TransformColumns(UnPivot, {{"Month", each Date.StartOfMonth(_), type date}}),
Pivot = Table.Pivot(StartOfMonth, List.Distinct(StartOfMonth[Attribute]), "Attribute", "Task Name", List.Count),
Combine = Table.Combine({Pivot,Table.FromColumns({List.Transform({1..12}, each Date.StartOfMonth(#date(2023,_,1)))},type table [Month= date])}),
Distinct = Table.Distinct(Combine, {"Month"}),
Sort = Table.Sort(Distinct,{{"Month", Order.Ascending}}),
MonthName = Table.TransformColumns(Sort, {{"Month", each Date.MonthName(_), type text}})
in
MonthName

[slorin]

Hi

let
Source = YourSource,
UnPivot = Table.UnpivotOtherColumns(Source, {"Task Name"}, "Attribute", "Value"),
StartOfMonth = Table.TransformColumns(UnPivot, {{"Value", each Date.StartOfMonth(_), type date}}),
Pivot = Table.Pivot(StartOfMonth, List.Distinct(StartOfMonth[Attribute]), "Attribute", "Task Name", List.Count),
List_Month = Table.FromColumns({List.Transform({1..12}, each Date.StartOfMonth(#date(2023,_,1)))},type table [Month= date]),
Join = Table.NestedJoin(List_Month, {"Month"}, Pivot, {"Value"}, "Table", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Table", {"Target Date", "Alert Date"}, {"Target Date", "Alert Date"}),
Sort = Table.Sort(Expand,{{"Month", Order.Ascending}}),
MonthName = Table.TransformColumns(Sort, {{"Month", each Date.MonthName(_), type text}})
in
MonthName

Stéphane 

[upniwesh]

Source : 

Task Name	Target Date	Alert Date
Task 1	13/06/2023	13/09/2023
Task 2	05/05/2023	10/08/2023
Task 3	10/05/2023	10/07/2023

If someone can help to get data by Power BI Query in the below format from the above table?

Required: 

Month	Alert Count	TargetCount
Jan	0	0
Feb	0	0
March	0	0
April	0	0
May	0	2
June	0	1
July	1	0
Aug	1	0
Sep	1	0
Oct	0	0
Nov	0	0
Dec	0	0

--

Thanks

Upniwesh

[Anonymous · Accepted solution]

Hi @upniwesh ,

Here's my solution in Power Query. 

1.Create a calendar table and extract months.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From("2023-12-31")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns1", "MonthName", each Date.MonthName([Dates])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Dates"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

2.Create two month name column and a custom column contains 1 in the main table.

=Date.MonthName([Target Date])

=Date.MonthName([Alert Date])

3.In Query1, click "Merge Queries".

Merge with the main table as follows.

4.Expand the custom column contains 1.

5.Merge again, replacing the matching column with Alert Month. After the merger is complete, expand the Alert Month column.

6.Group by MonthName and sum two columns.

Best Regards,

Stephen Tao

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