Power BI - Matrix count of values in query

Question

I am trying to create a matrix table that will count the number of jobs due in a month and also if they are completed on time.

At present I can get it this far.

I have tried summarizing data, creating measures etc. But to no avail.

I need to produce the following:

My data source is a series of transactions lines that include

1) Transaction Due date 2) Transaction completed date

Definition of terms:

Due in month        Count of due date
Done on time        Completed within due month
Outside time        completed outside due month
"%Perf"             percentage completed on time.

Any help with this would be much appreciated.

enter image description here

It will be helpful if you provide an image of your data model. DAX depends on data structures and their relations. — RADO
Thanks for your reply, all the information comes from a single table with the following relevant fields: [job_id]- Unique, [due_date], [completed], due date format DD/MM/YYYY, completed DD/MM/YYY hh:mm — Garry McKay
Can you please include 20 rows of data so we understand what you talk about. Also, what does Incomplete mean? — Aldert
The general premise is a job is booked in for a particular month, if it is completed within the due month it is considered "Done on time", if it is completed in any subsequent month it is "Outside time". If there is no completed date then it would be considered "Incomplete". I hope this helps to clarify my issue. - Data image added. — Garry McKay

Olly Olly · Accepted Answer · 2019-04-05T11:14:50

Add the following measures:

Measure "Due":

Due = COUNTROWS ( Table1 )

Measure "Done on time":

Done on time = 
VAR DueMonth = MONTH ( FIRSTDATE ( Table1[due_date] ) )
RETURN
    CALCULATE ( 
        [Due],
        FILTER ( 
            Table1,
            MONTH ( Table1[completed] ) = DueMonth
        )
    )

Measure "Outside time":

Outside time = 
VAR DueMonth = MONTH ( FIRSTDATE ( Table1[due_date] ) )
RETURN
    CALCULATE ( 
        [Due],
        FILTER ( 
            Table1,
            MONTH ( Table1[completed] ) <> DueMonth && 
            NOT ISBLANK ( Table1[completed] )
        )
    )

Measure "Incomplete":

Incomplete = 
VAR DueMonth = MONTH ( FIRSTDATE ( Table1[due_date] ) )
RETURN
    CALCULATE ( 
        [Due],
        FILTER ( 
            Table1,
            ISBLANK ( Table1[completed] )
        )
    )

Measure "% Perf"

% Perf = 
    DIVIDE ( 
        [Done on time],
        [Due],
        BLANK()
    )

Group by due_date.month in report.

See https://pwrbi.com/so_55513978/ for worked example PBIX file.

EDIT after updated question in comment:

There are many ways you could approach creating a visualisation which combines these measures, with the monthly counts by completion date, if you really must. Here's one approach:

Create a "Report Columns" table:

Report Columns = 
VAR ListMeasures = 
    DATATABLE ( 
        "Header", STRING,
        "Sort Order", INTEGER,
        "First Of Month", DATETIME,
        { 
            {"Due in month", 1, BLANK() }, 
            {"Done on time", 2, BLANK() }, 
            {"Outside time", 3, BLANK() }, 
            {"Incomplete", 4, BLANK() }, 
            {"% Perf", 5, BLANK() }
        }
    )
VAR ListMonths = 
    CALCULATETABLE(
        GROUPBY ( 
            ADDCOLUMNS ( 
                DISTINCT ( Table1[completed] ),
                "Header", FORMAT ( Table1[completed], "YYYY-MMM" ),
                "Sort Order", YEAR ( Table1[completed] ) * 100 + MONTH ( Table1[completed] ),
                "First Of Month", DATE ( YEAR ( Table1[completed] ), MONTH ( Table1[completed] ), 1 )
            ),
            [Header], [Sort Order], [First Of Month]
        ),
        Table1[completed] <> BLANK()
    )
RETURN 
    UNION ( 
        ListMeasures, 
        ListMonths 
    )

Set column Header to Sort By column Sort Order

Create Measure "Report Measure":

Report Measure = 
IF ( 
    NOT HASONEFILTER ( 'Report Columns'[Header] ), 
    BLANK(),
    SWITCH ( 
        VALUES ( 'Report Columns'[Header] ),
        "Due in month", [Due],
        "Done on time", [Done on time],
        "Outside time", [Outside time],
        "Incomplete", [Incomplete],
        "% Perf", [% Perf],
        CALCULATE ( 
            [Due],
            FILTER ( 
                Table1,
                DATE ( YEAR ( Table1[completed] ), MONTH ( Table1[completed] ), 1 ) = VALUES ( 'Report Columns'[First Of Month] )
            )
        )
    )
)

Add a matrix visualisation, with Table1[due_date] in rows, Report Columns[Header] in Columns, and [Report Measure] in values. Format to suit.

Updated example PBIX file: https://pwrbi.com/so_55513978-2/

Power BI - Matrix count of values in query

1 Answers

EDIT after updated question in comment: