百分比变化使用DAX

huangapple
go评论85阅读模式
英文:

Percentage Change using DAX

问题

  1. 日期            当月实际
  2. 31/01/2023    2,590,446.12
  3. 31/01/2023    47.40
  4. 31/01/2023    58.20
  5. 31/01/2023    38,471.94
  6. 31/01/2023    7,557.15
  7. 31/01/2023    6,870,250.38
  8. 31/01/2023    73,325.34
  9. 31/01/2023    5,754.04
  10. 31/01/2023    1,270,666.30
  11. 31/01/2023    426,068.41
  12. 28/02/2023    161.36
  13. 28/02/2023    7,557.15
  14. 28/02/2023    5,754.03
  15. 28/02/2023    426,068.78
  16. 28/02/2023    161.36
  17. 28/02/2023    7,557.15
  18. 28/02/2023    5,754.03
  19. 28/02/2023    426,068.78
  20. 28/02/2023    5,754.03
  21. 28/02/2023    426,068.78
  22. 31/03/2023    161.36
  23. 31/03/2023    38,026.78
  24. 31/03/2023    3,444.45
  25. 31/03/2023    779,195.00
  26. 31/03/2023    1,145,854.33
  27. 31/03/2023    26.88
  28. 31/03/2023    1,078,846.04
  29. 31/03/2023    7,626.63
  30. 31/03/2023    402,275.36
  31. 31/03/2023    1,021,696.88

如何在Power BI中计算三个月的百分比？

我已经编写了以下DAX公式，但似乎无法工作：

  1. InMonthActualPercentageChange =
  3. VAR CurrentMonthValue = CALCULATE(SUM(A120[当月实际]), LASTDATE(A120[日期]))
  5. VAR PreviousMonthValue = CALCULATE(SUM(A120[当月实际]), DATEADD(LASTDATE(A120[日期]), -1, MONTH))
  1. （代码部分未翻译）
英文: 
  1. Date		In Month Actual
  2. 31/01/2023	2,590,446.12
  3. 31/01/2023	47.40
  4. 31/01/2023	58.20
  5. 31/01/2023	38,471.94 
  6. 31/01/2023	7,557.15 
  7. 31/01/2023	6,870,250.38
  8. 31/01/2023	73,325.34 
  9. 31/01/2023	5,754.04
  10. 31/01/2023	1,270,666.30 
  11. 31/01/2023	426,068.41 
  12. 28/02/2023	161.36 
  13. 28/02/2023	7,557.15
  14. 28/02/2023	5,754.03
  15. 28/02/2023	426,068.78 
  16. 28/02/2023	161.36 
  17. 28/02/2023	7,557.15
  18. 28/02/2023	5,754.03
  19. 28/02/2023	426,068.78
  20. 28/02/2023	5,754.03
  21. 28/02/2023	426,068.78
  22. 31/03/2023	161.36
  23. 31/03/2023	38,026.78
  24. 31/03/2023	3,444.45 
  25. 31/03/2023	779,195.00
  26. 31/03/2023	1,145,854.33 
  27. 31/03/2023	26.88 
  28. 31/03/2023	1,078,846.04 
  29. 31/03/2023	7,626.63
  30. 31/03/2023	402,275.36 
  31. 31/03/2023	1,021,696.88

how do i calculate percentage in power bi over three months?

I have written this DAX formular but its doesn't seem to work:

  1. InMonthActualPercentageChange =
  3. VAR CurrentMonthValue = CALCULATE(SUM(A120[In Month Actual]), LASTDATE(A120[Date of Data]))
  5. VAR PreviousMonthValue = CALCULATE(SUM(A120[In Month Actual]), DATEADD(LASTDATE(A120[Date of Data]), -1, MONTH))

答案1

得分: 1

首先，创建一个单独的日期/日历表。

  1. Calendar = CALENDARAUTO()

在数据表的日期列和此表之间建立关系。

然后，更改度量中的日期引用，以引用 'Calendar'[date]。DATEADD() 函数需要一个具有连续间隔的日期列。

最后，在两个变量之后添加一个RETURN表达式，类似于以下内容：

  1. RETURN DIVIDE(CurrentMonthValue - PreviousMonthValue, PreviousMonthValue)

使用DIVIDE()而不是“/”运算符是最佳实践，因为该函数通过返回“Infinity”而不是错误来处理除以零。

---EDIT---

在日期表上放置一个月份列，并在可视化中使用它（例如，Month = EOMONTH('Date'[Date],0) 可以获取该月的最后一天）。此外，DATEADD()函数会移动传递给它的日期范围。如果传递单个日期，就会得到单个日期。可能更好的办法是同时传递整个月份。

对于当前月份，使用以下内容替代LASTDATE()

  1. DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -1, MONTH)

对于上个月份，将其向后移动一个月。整个度量看起来是这样的：

  1. InMonthActualPercentageChange =
  2. VAR BasePeriod = DATESINPERIOD( 'Date'[Date], LASTDATE( 'Date'[Date] ), -1, MONTH )
  3. VAR CurrentMonthValue =
  4.     CALCULATE ( SUM ( A120[In Month Actual] ), BasePeriod )
  5. VAR PreviousMonthValue =
  6.     CALCULATE (
  7.         SUM ( A120[In Month Actual] ),
  8.         DATEADD ( BasePeriod , -1, MONTH )
  9.     )
  10. RETURN
  11.     DIVIDE ( CurrentMonthValue - PreviousMonthValue, PreviousMonthValue )

这将强制审查期始终为一个月。

英文:

First, create a separate date/calendar table.

  1. Calendar = CALENDARAUTO()

Create a relationship between this and the date column of your data table.

Then change the date references in your measure to reference the 'Calendar'[date]. The DATEADD() function requires a date column with a contiguous interval.

Finally, add a RETURN expression after your two variables, something like so:

  1. RETURN DIVIDE( CurrentMonthValue - PreviousMonthValue, PreviousMonthValue )

Using DIVIDE() instead of a "/" operator is a best practice because the function will handle division by zero by returning "Infinity" instead of an error.

---EDIT---

Put a Month column on your Date table, and use that in your visual (e.g., Month = EOMONTH('Date'[Date],0) to get the last day of the month). Additionally, the DATEADD() function shifts the date range passed to it. If you pass a single date, you get a single date. May be better to pass the whole month to both.

For current month, use this in place of LASTDATE()

DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-1,MONTH)

And for prior month shift that back one month. The whole measure would then look like this:

  1. InMonthActualPercentageChange =
  2. VAR BasePeriod = DATESINPERIOD( 'Date'[Date], LASTDATE( 'Date'[Date] ), -1, MONTH )
  3. VAR CurrentMonthValue =
  4.     CALCULATE ( SUM ( A120[In Month Actual] ), BasePeriod )
  5. VAR PreviousMonthValue =
  6.     CALCULATE (
  7.         SUM ( A120[In Month Actual] ),
  8.         DATEADD ( BasePeriod , -1, MONTH )
  9.     )
  10. RETURN
  11.     DIVIDE ( CurrentMonthValue - PreviousMonthValue, PreviousMonthValue )

This forces the examination period to always be one month over one month.

huangapple
  • 本文由 发表于 2023年5月29日 05:25:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76353664.html
  • calculation
  • dax
  • percentage
  • powerbi
  • powerquery
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定