百分比变化使用DAX

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

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 =
  2. VAR CurrentMonthValue = CALCULATE(SUM(A120[当月实际]), LASTDATE(A120[日期]))
  3. 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 =
  2. VAR CurrentMonthValue = CALCULATE(SUM(A120[In Month Actual]), LASTDATE(A120[Date of Data]))
  3. 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
匿名

发表评论

匿名网友

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

确定