Power BI 上月滚动总计 DAX [英] Power BI Rolling Total Previous Month DAX

问题描述

我正在使用 POWER BI 并尝试计算上个月滚动总计的 DAX 表达式.我有一个过滤器，我选择某个月份，我想计算上个月的滚动总数.

I am working in POWER BI and trying to calculate a DAX expression for the rolling total of the previous month. I have a filter where I select a certain month, I would like to calculate the rolling total for the previous month.

以下是计算所选日期范围内滚动总计的完美方法.

Below is the calculation that works perfectly to calculate the rolling total for the selected date range.

如何计算前几个月的滚动总数?

How can I calculate the previous months rolling total?

Rolling_Total_Current_Month = CALCULATE(
        SUM(SalesInvoice[Sales])
        ,FILTER(ALLSELECTED(SalesInvoice), (SalesInvoice[Date]) <= MAX(SalesInvoice[Date])))

这是我的数据样本，我每天都有多个类别的销售额，(实际上我还有几列详细信息，但这是简化的)

Here is a sample of my data, I have sales per day, for multiple categories, (in fact i have a couple more columns of details but this is simplified)

Date    Day Amount  Category
1/1/2016    1   100 A
1/1/2016    1   120 B
1/1/2016    1   90  C
1/2/2016    2   500 A
1/2/2016    2   321 B
1/2/2016    2   143 C

到目前为止，我已经提出了一个求解滚动总数的方程式，但是当我尝试切片并查看单个类别的滚动总数时，它不适用于上个月.我只保留上个月的原始滚动总数.

So far I have come up with an equation to solve the rolling total, but when I try to slice is and view the rolling total of a single category it does not work for the previous month. I just keeps the original rolling total for the previous month.

这是适用于上个月滚动总数的公式.但一旦根据类别进行切片，就不会重新计算上个月的滚动总数.

Here is the equation for the rolling total previous month that works. But does not recalculate a rolling total for the previous month once sliced based on category.

PREVIOUS_MONTH_ROLLING_TOTAL = 
CALCULATE(
    [Current Sales]
    ,FILTER(
        ALL( Orders )
        ,Orders[MonthNumber] = MAX( Orders[MonthNumber] ) - 1
            && Orders[Day] <= MAX( Orders[Day] )
    )
)

推荐答案

首先，我在一个名为 Orders 的表中有这样的数据-

To start of, I have a data like this in a table called as Orders-

Date            Amount
12/12/2017      100
12/12/2017      200
12/12/2017      300
1/1/2018        400
1/1/2018        500

我首先创建一个名为 Year & 的计算列月份使用公式:-

I first create a calculated column called as Year & Month by using the formula:-

Year = YEAR(Orders[Date])
Month = FORMAT(Orders[Date],"mmmm")

然后我创建一个名为 Month Number 的列，当表中涉及一年以上时，它有助于排序以及识别以前的月份.

Then I create a column called as Month Number which will be helpful for sorting when more than one year is involved in the table and as well as to Identify the Previous Months.

MonthNumber = DATEDIFF(Min(Orders[Date]),Orders[Date],MONTH) 

当前月份销售额可以是度量或计算列.在问题中，您通过计算列获得了当月销售额的答案，如果您想进行衡量，那么类似的方法可以在汇总表上使用.

Current Month Sales can be a measure or a Calculated column. Qn the Question, you had your answer for current month sales via a calculated column and if you want to go for a measure then something like this would work on a summary table.

Current Month Sales = SUm(Orders[Amount])

我还会创建一个名为 Key 的列:-

I would also create a column called as Key:-

Key = Orders[MonthNumber] & Orders[Category]

现在，对于上个月的销售额，我将创建一个度量来查找我们创建的选定 MonthNumber.

Now, for the Previous Month Sales, I would create a measure that looks for selected MonthNumber that we created.

Previous Month Sales = 
         Var SelectedCategory = SELECTEDVALUE(Orders[Category])                                                                                                                                                           
         Var SelectedMonthNumberr = SELECTEDVALUE(Orders[MonthNumber]) - 1                                                                                                                                                
         Var ReqKey = SelectedMonthNumberr & SelectedCategory  

   Return 
         IF(ISBLANK(SelectedCategory) <> True(), 
         CALCULATE(SUM(Orders[Amount]),FILTER(ALL(Orders), Orders[Key] = ReqKey)),
         CALCULATE(SUM(Orders[Amount]),FILTER(ALL(Orders), Orders[MonthNumber] = SelectedMonthNumberr)))                                                                                                                                                                    

或你的措施

PREVIOUS_MONTH_ROLLING_TOTAL = 
CALCULATE(
    [Current Sales]
    ,FILTER(
        ALL( Orders )
        ,Orders[MonthNumber] = MAX( Orders[MonthNumber] ) - 1
            && Orders[Day] <= MAX( Orders[Day] )
    )
)

您可以将另一个过滤项添加为 &&Orders[Category] ​​= SELECTEDVALUE(Orders[Category]) 但当您没有选择任何类别或在没有类别的表格或视觉对象上时不起作用.因此，您需要在此处定义一个 if else 逻辑，正如我在度量公式中所引用的那样.

You can just add another filtering item as && Orders[Category] = SELECTEDVALUE(Orders[Category]) but won't work when you don't have any categories selected or on a table or visual which doesn't have categories. So, you would need to define an if else logic here as I have quoted on my measure formula.

如果这有帮助，请告诉我.

Do let me know, if this helps or not.

这篇关于Power BI 上月滚动总计 DAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！