如何根据PowerBi中的日期过滤器汇总数据

发布于 2025-02-09 21:31:51 字数 945 浏览 2 评论 0原文

我的示例CSV数据看起来像这样，因此您可以在下面使用相同的数据：

ID,Project,From,To,Percentage
1,APPLE,01-01-2022,31-03-2022,50
1,MICROSOFT,01-01-2022,15-01-2022,50
1,MICROSOFT,01-02-2022,28-02-2022,50
1,MICROSOFT,01-03-2022,31-03-2022,50
2,ORACLE,01-02-2022,23-06-2022,50
3,APPLE,23-04-2022,23-06-2022,100
1,MICROSOFT,16-01-2022,31-01-2022,50
2,DELL,01-12-2021,01-04-2022,50

我添加了一个新列，该列为此结果

CALCULATE(SUM('sample set'[Percentage]), FILTER('sample set', 'sample set'[ID]=EARLIER('sample set'[ID])&&EARLIER('sample set'[From]) <= 'sample set'[To]&&EARLIER('sample set'[To])>='sample set'[From]))

如下所示，如下所示，如下所示

。。

日期已将任何员工分配为100％以上的工作。如您所见，员工ID 1已在苹果中分配了1/1月22日至31/31/31/31/31/22的50％，在Microsoft中以50％本身分配了多个时期，但在任何时期内都没有大于100％。

但是在第一行中，分配的百分比显示为250％。这是因为滤波器标准符合ID 1的所有子周期。但是，如果您真的研究了它，则在任何子页面上都不是100％。因此，有没有办法可以获得真/错误或任何输出可以帮助我查看在一定时间内分配任何项目的任何员工，而分配的百分比总和超过100％？

原文

My sample CSV data looks like this, So that you can use the same data below:

ID,Project,From,To,Percentage
1,APPLE,01-01-2022,31-03-2022,50
1,MICROSOFT,01-01-2022,15-01-2022,50
1,MICROSOFT,01-02-2022,28-02-2022,50
1,MICROSOFT,01-03-2022,31-03-2022,50
2,ORACLE,01-02-2022,23-06-2022,50
3,APPLE,23-04-2022,23-06-2022,100
1,MICROSOFT,16-01-2022,31-01-2022,50
2,DELL,01-12-2021,01-04-2022,50

I added a new column for which the dax is

CALCULATE(SUM('sample set'[Percentage]), FILTER('sample set', 'sample set'[ID]=EARLIER('sample set'[ID])&&EARLIER('sample set'[From]) <= 'sample set'[To]&&EARLIER('sample set'[To])>='sample set'[From]))

The result is as shown below

My objective is to see if on any date any employee has been allocated for more than 100% in work. As you can see employee id 1 has been allocated 1/Jan/22 to 31/Mar/22 for 50% in APPLE and multiple periods in MICROSOFT for 50% itself but it doesn't for any period is greater than 100%.

But in the first line, the allocated percentage is shown as 250%. It is because the filter criteria meet all the sub-periods for ID 1. but if you really look into it, it is not 100% on any sub-period. So is there a way that I can get a TRUE/FALSE or any output that can help me see if any employee has been allocated for any project during a certain time that the allocated percentage sum is more than 100%?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一笔一画续写前缘 2025-02-16 21:31:51

（1）创建一个日历表

创建与模型无关的日历表。最简单的方法是进入造型色带并单击“新表”。 DAX很简单 - 如果要选择自己的开始/结束日期，请使用日历（）而不是calendarauto（）：

dimCalendar = CALENDARAUTO(12)

（2）创建措施以从日历表驱动

Total daily percentage = 

CALCULATE(
    SUM(DemoData[Percentage])
    , FILTER (DemoData, MAX('dimCalendar'[Date]) >= DemoData[From] && MIN('dimCalendar'[Date]) <= DemoData[To])
)

将您的度量设置为dimcalendar [date]和[id] - 或将其放入lint图表视觉效果或其他内容中。您将能够快速查看一条线的漂移位置超过100

。

(1) Create a calendar table

Create a calendar table that is not associated with your model. The easiest way is to just go into the Modeling ribbon and click 'new table.' The DAX is simple -- if you want to pick your own begin/end dates use CALENDAR() and not CALENDARAUTO() :

dimCalendar = CALENDARAUTO(12)

(2) Create your measure to drive from the calendar table

Total daily percentage = 

CALCULATE(
    SUM(DemoData[Percentage])
    , FILTER (DemoData, MAX('dimCalendar'[Date]) >= DemoData[From] && MIN('dimCalendar'[Date]) <= DemoData[To])
)

set your measure alongside dimCalendar[Date] and [ID] -- or put it in a line chart visual or whatever. You'll be able to quickly see where a line drifts over 100.

(Here I've added a line for ID #1 on Project 'Side-hustle')