Display different criteria of a single column in the same visual

[bitmode]

Hi there,

I'm new to PBI, solid with Excel and Access and right now unsure of how to approach my model or visual. I'd really appreciate advice on approach and happy to work out the methods as a way of learning.

Table columns:

ID = unique ID

Status = A (Commenced) or B = (Completed)

Commencement_date

Completion_date

The goal is a visual (Graph) where Count of a column (ID) is in Y and date is X. Where currently I can display this, with the Count filtered by a Page filter, what I'm aiming for is to illustrate on a graph both Count of ID (where status = A) and Count of ID (where status = B). So for any specific date or period, I can see how many Commenced, and how many Completed.

I attempted to create Columns in my table for each status (A and B, containing true or false) then adding those to the visual (column values and line values). The count for both is the same as I'm unable to set a criteria for either (count of A where true, for example). I've also pursued trying to create a custom query or table, but I don't know how that should be structured - in Excel I'd previously have two countifs in a new table that informs a pivottable or chart. I guess I'm missing a fundamental understanding of a custom query/table in the context of PowerBI?

Would anyone be able to point me in the right direction, or best practice for approaching this kind of visual requirement via the right techniques in PBI?

Regards,

Steve

[Zubair_Muhammad · Accepted solution]

Hi @bitmode

I believe if you unpivot Commencement Date and Completion Date into a single Date Column, it will make it easy for you to do the COUNT.

Could you paste some sample data and expected results?

[bitmode]

Thank you Zubair, this pointed me in the right direction. As a result, in order to have some granularity to the X (date) axis, I had to utilise a new Date table related to the now-unpivoted table, as the alternative was a value every day for which a 'commencement' or 'completion' existed.

Thanks for the assist!