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

问题描述

我要显示两列。第一个是关闭日期，另一个是打开数据。我需要计算每月关闭和每月打开的数量，然后将它们放入堆叠的图表中。问题是关闭/打开月份不同。

为了更好地解释它，我提供了一个简短的摘录：

  CLOSE_DTTM OPEN_DT 
 2018年8月1日24/05/2018 
 01/08/2018 31/05/2018 
 2018年8月2日2018年12月7日
 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 
  

我应该为堆栈图中的轴使用什么？

解决方案

我建议创建一个日历表。

 日期= CALENDARAUTO（）
  

在轴上使用 Dates [Date] 月创建两个要放入值部分的度量：

  OpenCount = COUNTROWS（FILTER（Table1，Table1 [OPEN_DT] IN DATESMTD（Dates [Date]）））
 CloseCount = COUNTROWS（FILTER（Table1，Table1 [CLOSE_DTTM] IN DATESMTD（Dates [Date]））））
  

然后您的图表应如下所示：

将新的日期列放在轴上的月份，新的 Type 列作为图例，在值部分中，计数为 Date 或计数为 Type 。

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()

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