CODEWITHSUNDEEP
powerbi
Kalenji
Kalenji

Reputation: 407

Power BI - show data per month

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?

Upvotes: 1

Views: 2985

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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:

Stacked Bar Chart 1

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

Unpivotted Data

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.

Stacked Bar Chart 2

Upvotes: 1

Related Questions