时间戳

首页 > 解决方案 > 如何动态更改 PowerBI 中 DATEADD 函数的间隔类型？

powerbi - 如何动态更改 PowerBI 中 DATEADD 函数的间隔类型？

问题描述

我想让我的用户能够选择是否将当前数据与上个月或上一年的数据进行比较。此 DAX 度量是用 PowerBI 编写的。以下代码片段使用用户选择的 'MarketShare' 和 'Match' 来确定计算，并使用用户选择的 'Compare' 来确定是否与上个月或上一年进行比较，但效率不高，因为我重复了 'ScriptTypeVar ' 月份和年份版本的变量。

Previous Amount Scripted = 
VAR SelectedMatch = FIRSTNONBLANK(Match[Match],1)
VAR SelectedMarketShare = FIRSTNONBLANK('Market Share'[Market Share],1)
VAR SelectedCompare = FIRSTNONBLANK('Compare'[Compare],1)


VAR PreviousMonthScriptTypeVar = SWITCH(TRUE(),
                SelectedMarketShare = "Quantity", CALCULATE(SUM(Scripts_Fact[Pack Quantity]),DATEADD('Date_Dimension'[Date], -1, ,MONTH)),
                SelectedMarketShare = "Quantity (Repeats)", CALCULATE(SUM(Scripts_Fact[Pack Quantity (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,MONTH)),
                SelectedMarketShare = "Value", CALCULATE(SUM(Scripts_Fact[Value]),DATEADD('Date_Dimension'[Date], -1, ,MONTH)),
                SelectedMarketShare = "Value (Repeats)", CALCULATE(SUM(Scripts_Fact[Value (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,MONTH))
            )

VAR PreviousYearScriptTypeVar = SWITCH(TRUE(),
                SelectedMarketShare = "Quantity", CALCULATE(SUM(Scripts_Fact[Pack Quantity]),DATEADD('Date_Dimension'[Date], -1, ,YEAR)),
                SelectedMarketShare = "Quantity (Repeats)", CALCULATE(SUM(Scripts_Fact[Pack Quantity (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,YEAR)),
                SelectedMarketShare = "Value", CALCULATE(SUM(Scripts_Fact[Value]),DATEADD('Date_Dimension'[Date], -1, ,YEAR)),
                SelectedMarketShare = "Value (Repeats)", CALCULATE(SUM(Scripts_Fact[Value (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,YEAR))
            )

VAR CompareTypeVar = SWITCH(TRUE(),
                SelectedCompare = "Previous Month", PreviousMonthScriptTypeVar,
                SelectedCompare = "Same Month Previous Year", PreviousYearScriptTypeVar
            )


VAR AmountScriptedVar = 
IF(AND(HASONEVALUE('Calendar'[Year]),HASONEVALUE('Calendar'[Month])),
                                SWITCH(TRUE(),
                                    SelectedMatch = "A", CALCULATE(CompareTypeVar,USERELATIONSHIP('Reporting_Products'[A], Product_Dimension[A])),
                                    SelectedMatch = "B", CALCULATE(CompareTypeVar,USERELATIONSHIP(Reporting_Products[B], Product_Dimension[B])),
                                    SelectedMatch = "C", CALCULATE(CompareTypeVar,USERELATIONSHIP('Reporting_Products'[C], Product_Dimension[C])),
                                    SelectedMatch = "D", CALCULATE(CompareTypeVar,USERELATIONSHIP('Reporting_Products'[D], Product_Dimension[D]))
                                    ),
                                [AmountScripted]
                            )
RETURN
AmountScriptedVar

我尝试使用“CompareTypeVar”开关返回 MONTH 或 YEAR 关键字并简化测量，如下所示：

Previous Amount Scripted = 
VAR SelectedMatch = FIRSTNONBLANK(Match[Match],1)
VAR SelectedMarketShare = FIRSTNONBLANK('Market Share'[Market Share],1)
VAR SelectedCompare = FIRSTNONBLANK('Compare'[Compare],1)

VAR CompareTypeVar = SWITCH(TRUE(),
                SelectedCompare = "Previous Month", MONTH,
                SelectedCompare = "Same Month Previous Year", YEAR
            )

VAR ScriptTypeVar = SWITCH(TRUE(),
                SelectedMarketShare = "Quantity", CALCULATE(SUM(Scripts_Fact[Pack Quantity]),DATEADD('Date_Dimension'[Date], -1, ,CompareTypeVar)),
                SelectedMarketShare = "Quantity (Repeats)", CALCULATE(SUM(Scripts_Fact[Pack Quantity (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,CompareTypeVar)),
                SelectedMarketShare = "Value", CALCULATE(SUM(Scripts_Fact[Value]),DATEADD('Date_Dimension'[Date], -1, ,CompareTypeVar)),
                SelectedMarketShare = "Value (Repeats)", CALCULATE(SUM(Scripts_Fact[Value (Repeats)]),DATEADD('Date_Dimension'[Date], -1, ,CompareTypeVar))
            )               

VAR AmountScriptedVar = IF(AND(HASONEVALUE('Calendar'[Year]),HASONEVALUE('Calendar'[Month])),
                                SWITCH(TRUE(),
                                    SelectedMatch = "Generic Code", CALCULATE(ScriptTypeVar,USERELATIONSHIP('Reporting_Products'[Medprax Generic Code], Product_Dimension[Medprax Generic Code])),
                                    SelectedMatch = "ATC5 Code", CALCULATE(ScriptTypeVar,USERELATIONSHIP(Reporting_Products[ATC5 Code], Product_Dimension[ATC5 Code])),
                                    SelectedMatch = "ATC4 Code", CALCULATE(ScriptTypeVar,USERELATIONSHIP('Reporting_Products'[ATC4 Code], Product_Dimension[ATC4 Code])),
                                    SelectedMatch = "ATM Link", CALCULATE(ScriptTypeVar,USERELATIONSHIP('Reporting_Products'[ATM Link], Product_Dimension[ATM Link]))
                                    ),
                                [AmountScripted]
                            )
RETURN
AmountScriptedVar

...但我收到以下错误：

The last argument must be one of these keywords: DAY, MONTH, QUARTER, or YEAR.

我已经搜索但找不到动态更改间隔关键字的解决方案。我还尝试在 DATEADD 函数中使用 if 语句直接返回关键字而不使用变量，但返回了相同的错误。任何帮助将不胜感激。

尽管初始版本有效，但未来的项目可能会为用户提供更多选择，并且需要对更多虚拟变量进行编码。如果每个用户选择都可以由 switch 语句处理并组合以创建最终计算，如第二个版本所示，那将是理想的。

标签： powerbidax

解决方案

我不确定是否可以将最后一个参数设为变量。我建议改用以下方法。

Previous Amount Scripted =
<...>
VAR CompareDatesTable =
    SWITCH (
        TRUE (),
        SelectedCompare = "Previous Month",
        DATEADD ( 'Date_Dimension'[Date], -1, MONTH ),
        SelectedCompare = "Same Month Previous Year",
        DATEADD ( 'Date_Dimension'[Date], -1, YEAR)
    )
VAR ScriptTypeVar =
    SWITCH (
        TRUE (),
        SelectedMarketShare = "Quantity",
        CALCULATE (
            SUM ( Scripts_Fact[Pack Quantity] ),
            CompareDatesTable
        ),
        <...>
    )
<...>

在上面，我将整个表传递CompareDatesTable给CALCULATE函数。

推荐阅读