Power BI DAX汇总并按日期过滤 [英] Power BI DAX summarize and filter by date

查看:297
本文介绍了Power BI DAX汇总并按日期过滤的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个Power BI报告,并且我想查找发生某个动作的上次日期,该动作的值非零.在下面的示例中:

I have a Power BI report and I want to find the last date some action took place that had a non-0 value. In the following example:

|-------------|------------|
|  ActionDate |   ActionAmt|
|-------------|------------|
| 1-Feb       |  -100      |
|-------------|------------|
| 1-Feb       |   100      |
|-------------|------------|
| 10-Jan      |   150      |
|-------------|------------|

我想返回10月1日的日期,而不是2月1日,因为10月1日是ActionDay求和的第一个非0值.我的代码返回1月2日:

I want to return the 10-Jan date, not 1-Feb, since 10-Jan is the first non-0 value summed by ActionDay. My code returns 1-Feb:

Last Action Date =
VAR maxdatekey =
    CALCULATE ( MAX ( 'Action'[ActionDateKey] ), 'Action'[ActionAmount] > 0 )
RETURN
    IF (
        maxdatekey,
        FORMAT (
            LOOKUPVALUE ( 'Date'[DateValue], 'Date'[DateKey], maxdatekey ),
            "dd-MMM-yyyy"
        )
    )

如何将其进一步分组以排除汇总的0天?

How do I further group this to exclude the summarized 0 days?

推荐答案

我认为您正在寻找类似这样的东西,您需要在计算金额时按日期进行汇总:

I think you're looking for something like this where you summarize by date when calculating the amount:

LastActionDate =
VAR Summary =
    SUMMARIZE (
        'Date',
        'Date'[DateValue],
        "Amt", CALCULATE ( SUM ( 'Action'[ActionAmount] ) )
    )
RETURN
    FORMAT (
        MAXX ( FILTER ( Summary, [Amt] > 0 ), 'Date'[DateValue] ),
        "dd-MM-yyyy"
    )

这篇关于Power BI DAX汇总并按日期过滤的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆