Microsoft Power BI - DAX 时间智能测量 - 更改上下文以反映适当的百分比变化；非 YTD 措施 [英] Microsoft Power BI - DAX Time Intelligence measure - change context to reflect proper % change; non-YTD measures

问题描述

我有一个如下所示的 Power BI 视觉对象.有3个矩阵.我有一个 DateDimension(或)日历表，名为 Dates2.

I have a Power BI visual as below. There are 3 matrices. I have a DateDimension (or) Calendar table called Dates2.

我使用两种度量，一种是常规度量(称为计数")，另一种是度量的并行周期比较(称为Count_PreviousYear").我对后者使用 SAMEPERIODLASTYEAR DAX 函数.

I use two measures, one a regular measure (called 'Count'), the other a parallel period comparison of the measure (called 'Count_PreviousYear'). I use SAMEPERIODLASTYEAR DAX function for the latter.

1)

Count = COUNTA(TableX[ColumnY])

--用名称'Count'测量--

--Measure with name 'Count'--

2)

Count_PreviousYear = CALCULATE
  (
    [Count],
    SAMEPERIODLASTYEAR(Dates2[Date])
  )

--使用名称Count_PreviousYear"进行测量

--Measure with name 'Count_PreviousYear'

--此度量使用时间智能函数 - SAMEPERIODLASTYEAR--

--this measure uses Time Intelligence function - SAMEPERIODLASTYEAR--

Count"和Count_PreviousYear"(显然)都不是 YTD(YearToDate)值.

Both 'Count' and 'Count_PreviousYear' (obviously) are not YTD (YearToDate) values.

跨时期百分比变化的第三个度量计算如下:

A third measure for the percentage change across periods is computed as below:

3)

PercentageChange = IF(

    ISBLANK([Count]) || ISBLANK([Count_PreviousYear]),

    BLANK(),

    (([Count] - [Count_PreviousYear])/[Count])

)

请忽略关键字用作度量名称的事实；为了清楚起见，我使用了计数"这个名称；在我的实际报告中，我有专有名词

Kindly ignore the fact that a keyword used as a measure name; I have used the name 'Count' only for clarity; in my actual report, I have proper names

% 变化度量工作正常，但有一个问题:

The % change measure works fine, but one issue:

对于 2020 年到 2021 年期间的变化，即在最后一个矩阵的第三行(对于行值 2021)，总计(即百分比变化值)不合适.

For the period change from 2020 to 2021, i.e. in the third row of the last matrix (for the row value 2021), the total (i.e. the % change value) is not appropriate.

我需要将 -737.21% 替换为 - 23.98 %.

I need to replace -737.21% with - 23.98 %.

这是因为，我需要计算 2020 年的总计，只需将 1 月和 2 月的值相加，即 428 + 430 = 858.(不是 5794，这是所有 12 个月的值).

This is because , I need to compute the Total for 2020, only by adding the values for the months of January and February, i.e. 428 + 430 = 858. (not 5794, which is for all the 12 months).

由于 2021 年只有两个月 - 一月和二月，我不想将 2021 年的两个月与 2020 年的所有 12 个月进行比较.相反，我希望将 2021 年的两个月与相应的 2 个月进行比较2020 年.

Since 2021 has only two months - January and February, I don't want to compare two months of 2021, with all the 12 months of 2020. Rather, I want two months of 2021 to be compared with the corresponding 2 months of 2020.

基本上我需要 {(692-858)/692} * 100 = -23.98%

目前，我看到 {(692-5794)/692} * 100 = -737.21%

有人可以帮我实现吗?

推荐答案

Count Previous Year =
IF (
    HASONEVALUE ( Dates2[Month] ),
    IF (
        [Count] <> BLANK (),
        CALCULATE ( [Count], SAMEPERIODLASTYEAR ( Dates2[Date] ) )
    ),
    IF (
        HASONEVALUE ( Dates2[Year] ),
        CALCULATE (
            [Count],
            DATESBETWEEN (
                Dates2[Date],
                EDATE ( MIN ( Dates2[Date] ), -12 ),
                EOMONTH ( MAX ( [FactTable[Date] ), -12 )
            )
        )
    )
)

    

这篇关于Microsoft Power BI - DAX 时间智能测量 - 更改上下文以反映适当的百分比变化；非 YTD 措施的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！