每个月最后日期(MAX 日期)值的平均值 [英] Average of Each months last date (MAX date) value

问题描述

我希望计算每个月的最后日期(最大日期)值的 AVG.以下是样本数据.

I am looking to calculate AVG OF EACH Month's last date (MAX date) value. Below is the sample data.

请有人帮助我进行 DAX 计算.非常感激.

Can you please someone help me with the DAX caluclations. Much appreciated. 

如果您需要更多信息，请告诉我.

Please let me know if you need more information.

Location    DATE           NAME  VALUE  
P1         1/1/2019 0:00    ABC   12    
P1  1/2/2019 0:00   ABC 4   
P1  1/3/2019 0:00   ABC 50  
P1  1/4/2019 0:00   ABC 8   
P1  1/5/2019 0:00   ABC 35  ABC MaxDate value of JAN
P1  1/1/2019 0:00   DEF 20  
P1  1/2/2019 0:00   DEF 25  
P1  1/3/2019 0:00   DEF 66  
P1  1/4/2019 0:00   DEF 24  
P1  1/5/2019 0:00   DEF 50  DEF MaxDate value of JAN
P2  2/1/2018 0:00   ABC 28  
P2  2/2/2018 0:00   ABC 82  
P2  2/3/2018 0:00   ABC 67  
P2  2/4/2018 0:00   ABC 43  
P2  2/5/2018 0:00   ABC 66  ABC MaxDate value of FEB
P2  2/1/2018 0:00   DEF 28  
P2  2/2/2018 0:00   DEF 82  
P2  2/3/2018 0:00   DEF 67  
P2  2/4/2018 0:00   DEF 43  
P2  2/5/2018 0:00   DEF 34  DEF MaxDate value of FEB

我需要的是年初至今每个月的最大日期值的平均值ABC 年初至今 = AVG(35+66)
DEF YTD = AVG(50+34)

what I need is Average of Max date values of each month as YTD ABC YTD = AVG(35+66)
DEF YTD = AVG(50+34)

我需要的是年初至今每个月的最大日期值的平均值ABC 年初至今 = AVG(35+66)
DEF YTD = AVG(50+34)

what I need is Average of Max date values of each month as YTD ABC YTD = AVG(35+66)
DEF YTD = AVG(50+34)

推荐答案

注意:数据表，或数据"是指您在问题中提供的数据表.

N.B: The Data table, or 'Data' refers to the data table you provided in the question.

方法一:

这样总结数据表:

SummarizedTable = 
SUMMARIZE(
    ADDCOLUMNS(
        'Data';
        "Month"; MONTH([DATE]);
        "Year"; YEAR([DATE])
    );
    [Year];
    [Month];
    [NAME];
    "MaxDateValue" = 
    var MaxD = MAX([DATE])
    return
    CALCULATE(
        SUM('Data'[VALUE]);
        'Data'[DATE] = MaxD
    )
)

您最终会得到一个如下所示的新表:

You end up with a new table looking like this:

在您的仪表板上，您可以通过从新表 'SummarizedTable' 拖放来构建具有 [NAME] 和 [MaxDateValue] 的可视表代码>.右键单击可视化窗格中的 [MaxDateValue] 并选择平均值而不是总和，您最终应该会得到一个如下所示的可视表:

On your dashboard you can build a visual table with [NAME] and [MaxDateValue] by drag and drop from your new table 'SummarizedTable'. Right click on [MaxDateValue] in the visualizing pane and select average instead of sum and you should end up with a visual table looking like this:

方法二:

您可以将其转换为度量，而不是使用汇总计算新表.这样做的好处是您保留了原始数据的完整性，这使得切片更容易.

Instead of calculating a new table using summarize you can turn it into a measure. The benefit of this is that you retain the integrity of the original data and this makes it easier to slice.

AvgYtd = 
AVERAGEX(
    SUMMARIZE(
        ADDCOLUMNS(
            'Data';
            "Month"; MONTH([DATE]);
            "Year"; YEAR([DATE])
        );
        [Year];
        [Month];
        [NAME];
        "MaxDateValue" = 
        var MaxD = MAX([DATE])
        return
        CALCULATE(
            SUM('Data'[VALUE]);
            'Data'[DATE] = MaxD
        )
    );
    [MaxDateValue]
)

在您的仪表板上，您现在可以通过从原始数据表中拖动 [NAME] 然后添加您的新度量 [AvgYtd] 来创建一个新的可视表，以便您有两个表显示相同的内容:

On your dashboard you can now create a new visual table by dragging [NAME] from the original Data table and then adding your new measure [AvgYtd] so that you have two tables showing the same thing:

不过，您现在可以添加切片器，例如[Location] 从数据表中选择 P1:

However, you can now add a slicer, e.g. [Location] from the Data table and select P1:

您可以看到使用度量创建的可视化表受切片器影响，而汇总表则不受.

You can see that the visual table created using the measure is affected by the slicer, while the summarized table is not.

这篇关于每个月最后日期(MAX 日期)值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！