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")
然后，我创建了一个列，称为月号，这对在表中涉及一年以上时进行排序，并确定上个月。
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)
当月销售额可以衡量或计算列。在问题 Qn中，您可以通过计算列获得当月销售额的答案，如果要进行度量，则类似的内容将在汇总表中起作用。
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])
我还将创建一个称为键的列：-
I would also create a column called as Key:-
Key = Orders[MonthNumber] & Orders[Category]
现在，对于上个月的销售，我将创建一个度量来查找选定的我们创建的月份编号。
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 if逻辑，就像我在度量公式中引用的那样。
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屋！
