Microsoft Power BI

本文介绍了Microsoft Power BI-DAX时间智能度量-更改上下文以反映适当的％更改；非年初至今的措施的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个Power BI视觉效果，如下所示.有3个矩阵.我有一个名为Dates2的DateDimension(或)Calendar表.

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

1)

Count = COUNTA(TableX [ColumnY])

-名称为"Count"的度量-

2)

Count_PreviousYear = CALCULATE([数数]，SAMEPERIODLASTYEAR(Dates2 [Date]))

-名称为"Count_PreviousYear"的测量

-此度量使用时间智能功能-SAMEPERIODLASTYEAR-

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

第三个时间段百分比变化量度的计算方法如下:

3)

PercentageChange = IF(ISBLANK([Count])||ISBLANK([Count_PreviousYear])，空白的()，(([[Count]-[Count_PreviousYear])/[Count]))

请忽略关键字用作度量名称的事实.我只是为了清楚起见使用了"Count"这个名字.在我的实际报告中我有个好名字

％更改量度工作正常，但是有一个问题:

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

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

这是因为，我只需要将一月和二月的值相加即可计算出2020年的总数，即428 + 430 =858.(不是5794，这是所有12个月的总和)./p>

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

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

当前，我看到的是{(692-5794)/692} * 100 = -737.21％

有人可以帮助我实现这一目标吗?

解决方案

计数上一年=如果 (HASONEVALUE(Dates2 [Month])，如果 ([Count]<>空白的 ()，计算([计数]，SAMEPERIODLASTYEAR(Dates2 [Date])))，如果 (HASONEVALUE(Dates2 [Year])，计算 ([数数]，DATESBETWEEN(Dates2 [Date]，EDATE(MIN(Dates2 [Date])，-12)，EOMONTH(MAX([FactTable [Date])，-12)))))

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

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

--Measure with name 'Count'--

2)

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

--Measure with name 'Count_PreviousYear'

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

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:

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.

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

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

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.

Essentially I need {(692-858)/692} * 100 = -23.98%

Currently, I see {(692-5794)/692} * 100 = -737.21%

Can someone help me achieve this?

解决方案

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