Power BI - 每月显示数据 [英] Power BI - show data per month

问题描述

我要显示两列.第一个是Close date，另一个是Open data.我需要计算每月关闭和每月打开并将它们放入堆叠图表中.问题是收盘/开盘月份不一样.

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?

推荐答案

我建议创建一个日历表.

I'd suggest creating a calendar table.

Dates = CALENDARAUTO()

使用轴上的 Dates[Date] 月份并创建两个度量以放入 Values 部分:

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:

将新的Date列Month放在Axis上，新的Type列作为Legend，Date的计数或<的计数值部分中的代码>类型.这应该会给出一个类似的图表.

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 - 每月显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！