Stack column chart in PowerBI with date on x-asix and count on y-axis

Question

I have a data in below format and looking to get a stacked column chart as below. I am not sure how to club the dates and count together which are in multiple columns.

My data

I want to represent the above data in a clustered column chart where data on X-axis is clubbed in month and on Y-axis is clubbed with count of Yes per month like below based on different project types.

Answer 1

You will need to transform your data, add a Date table, add a relationship, and possibly a measure to do this.

1. Transform your data
You should unpivot your columns in PowerQuery to give you a table structure similar to this:

Project	Test	Date	Result
A	Test A	01/05/2023	Yes

Here is an example query to do this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw1TcyMDIGciJTi4GkiT5QAMEFoVidaCUnKBuGDI31DcwQCkFKnEHC+hbIug2N9A0NUAQwdLkA2UbG+oaG6LYChVFEY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Test A Date Approved" = _t, #"Test A Result" = _t, #"Test B Date Approved" = _t, #"Test B Result" = _t, #"Test C Date Approved" = _t, #"Test C Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Test A Date Approved", type date}, {"Test A Result", type text}, {"Test B Date Approved", type date}, {"Test B Result", type text}, {"Test C Date Approved", type date}, {"Test C Result", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Date Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Attribute], "Approved")),
    #"Date Renamed Columns" = Table.RenameColumns(#"Date Filtered Rows",{{"Value", "Date"}, {"Attribute", "Test"}}),
    #"Date Replaced Value" = Table.ReplaceValue(#"Date Renamed Columns"," Date Approved","",Replacer.ReplaceText,{"Test"}),
    #"Result Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each Text.Contains([Attribute], "Result")),
    #"Result Renamed Columns" = Table.RenameColumns(#"Result Filtered Rows",{{"Attribute", "Test"}, {"Value", "Result"}}),
    #"Result Replaced Value" = Table.ReplaceValue(#"Result Renamed Columns"," Result","",Replacer.ReplaceText,{"Test"}),
    Merge = Table.NestedJoin(#"Date Replaced Value", {"Project", "Test"}, #"Result Replaced Value", {"Project", "Test"}, "ResultMerge", JoinKind.LeftOuter),
    #"Expanded ResultMerge" = Table.ExpandTableColumn(Merge, "ResultMerge", {"Result"}, {"Result"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ResultMerge",{{"Date", type date}, {"Result", type text}})
in
    #"Changed Type1"

2. Create a Date table
There are multiple ways to do this. For example, you can create a Calculated Table with the following DAX:

Dim Date = ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "mmmm"),
    "Month#", MONTH([Date])
  )

Then right-click on this new Dim Date table and Mark as date table, select Date as the Date column. Next, select Month column and in the ribbon select Sort by column and select the Month# column.

3. Create a relationship
Now create a relationship between your Dim Date table to your test results table on the Date columns in each. It should look like:

4. Create your chart
Now you will be able to create your chart as show below. Select Show items with no data to see consecutive months.