Power BI - show data per month

Question

I have two columns I'm trying to display. The first one is Close date and the other one is Open data. I need to count closed per month and opened per month and put them into a stacked chart. The issue is that close / open month are not the same.

To explain it better, I have included a quick extract:

    CLOSE_DTTM  OPEN_DT
01/08/2018  24/05/2018
01/08/2018  31/05/2018
02/08/2018  12/07/2018
02/08/2018  12/07/2018
01/08/2018  26/07/2018
02/08/2018  28/07/2018
02/08/2018  29/07/2018
02/08/2018  31/07/2018
02/08/2018  31/07/2018
02/08/2018  01/08/2018

What should I use for the axis in a stack graph?

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-31T15:43:38

I'd suggest creating a calendar table.

Dates = CALENDARAUTO()

Use the Dates[Date] Month on the Axis and create two measures to put in the Values section:

OpenCount  = COUNTROWS(FILTER(Table1, Table1[OPEN_DT] IN DATESMTD(Dates[Date])))
CloseCount = COUNTROWS(FILTER(Table1, Table1[CLOSE_DTTM] IN DATESMTD(Dates[Date])))

Then your chart should look like this:

Another option is to unpivot your two columns in the query editor:

Put the new Date column Month on the Axis, the new Type column as the Legend, and count of Date or count of Type in the Values section. This should give a similar chart.

Power BI - show data per month

1 Answers