I am working with a PowerPivot model where I want to add date intelligence formulas using DAX. I could build the formula and after checking it, Power Pivot says that are ok, nevertheless, the formulas are returning values that are not consistency with what I am expecting.
我正在使用PowerPivot模型,我想使用DAX添加日期智能公式。我可以构建公式并在检查之后,Power Pivot说没关系,但是,公式返回的值与我期望的不一致。
YTD Formula:
=CALCULATE(TOTALYTD(SUM(V_CONTABILIDAD_EstadosFinancieros[Saldo]),V_CONTABILIDAD_EstadosFinancieros[FinPeriodo]))
The error is that the value that is returning is the same value as Saldo, it means is not accumulating the amount over months.
错误是返回的值与Saldo的值相同,这意味着不会累积数月的金额。
Last Year Formula
去年的公式
=CALCULATE(SUM(V_CONTABILIDAD_EstadosFinancieros[Saldo]),DATEADD(V_CONTABILIDAD_EstadosFinancieros[InicioPeriodo],-1,YEAR))
The error is that is not giving me any value. The fields InicioPeriodo and FinPeriodo as marked as dates.
错误是没有给我任何价值。字段InicioPeriodo和FinPeriodo标记为日期。
Please your advice if I need to check something additional in the model in order that these functions work.
如果我需要检查模型中的其他内容以便这些功能起作用,请提出您的建议。
Thanks in advance.
提前致谢。
1 个解决方案
#1
0
Ok, I resolved it.
好的,我解决了。
It is not about using date tables in the formulas, it should be used in the pivot tables as well, as slicers and fields.
它不是在公式中使用日期表,它也应该在数据透视表中用作切片器和字段。
Here the details http://www.powerpivotpro.com/2013/01/calendar-tables-not-just-for-formulas-use-them-on-your-pivots-too/
详细信息http://www.powerpivotpro.com/2013/01/calendar-tables-not-just-for-formulas-use-them-on-your-pivots-too/
Now is working beautifully.
现在工作得很漂亮。
#1
0
Ok, I resolved it.
好的,我解决了。
It is not about using date tables in the formulas, it should be used in the pivot tables as well, as slicers and fields.
它不是在公式中使用日期表,它也应该在数据透视表中用作切片器和字段。
Here the details http://www.powerpivotpro.com/2013/01/calendar-tables-not-just-for-formulas-use-them-on-your-pivots-too/
详细信息http://www.powerpivotpro.com/2013/01/calendar-tables-not-just-for-formulas-use-them-on-your-pivots-too/
Now is working beautifully.
现在工作得很漂亮。