我有一个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 ) ) ) ) )
更多推荐
Microsoft Power BI
发布评论