英文:
How to model "current month" so conditional formats ignore future months in PowerBI Matrix
问题
我正在生成一个矩阵/表格，显示每月实际数据与两个财务预测之间的比较。我需要根据实际数据与预测数据之间的差异（+/- 100）来有条件地设置背景颜色。我有一些维度表和3个事实表。
在我的FactActuals表中，我创建了一个简单的度量，用于计算实际数据与预测1之间的差异：
DiffToFC1 = SUM(FactForecast1[Amount])-SUM(FactActuals[Amount])
有条件的背景规则如下：
这个方法有效，但对于尚未发生的月份，格式会延伸到未来，而我永远不会有实际数据。我的“当前月份”是一个可以从我的数据中获取的常数，但我不确定如何以及在哪里建模该值，以便正确应用条件格式。它不是“Today()”...可以从我的源数据中提取或推断，但我不知道如何公开它。
问题：
如何建模“当前月份”并在矩阵可视化值的条件背景计算中引用它，以便忽略未来的月份？ 过去的空值应被视为零以进行样式设置，而未来的空值应被忽略。
我更新了度量以排除空值，使用以下公式：
DiffToFC1-ISBLANK = IF(NOT(ISBLANK(SUM(FactActuals[Amount]))), SUM(FactForecast1[Amount])-SUM(FactActuals[Amount]))
这可以防止未来的空值显示，但不允许历史月份被视为零，以进行条件格式设置。在链接的示例文件中，2022年4月是一个没有实际数据的历史月份，但应该被突出显示。
英文:
I am generating a matrix/table showing monthly actuals compared to two financial forecasts. I need to conditionally format the backround color in scenarios where the ACTUALS are off by +/- 100. I have a few dimension tables and 3 fact tables.
Within my FactActuals table, I created a simple measure to calculate the difference between the actuals and forecast1:
DiffToFC1 = SUM(FactForecast1[Amount])-sum(FactActuals[Amount])
The conditional background rules are:
This works except the formatting pushes out into the future for months that have not occurred... for which I would never have actuals. My "current month" is a single constant that I can retrieve from my data but I am uncertain how and where to model that value so conditional formatting can be applied correctly. It is not "Today()"... it can be pulled or inferred from my source data but I don't know how to expose it.
Question
How to model "current month" and reference it in conditional background calculations for matrix visualization values so future months are ignored? Null/Blank values in past should be considered zero for the purpose of styling... while null/bank values in future are ignored.
I updated the measure to exclude blanks using the following:
DiffToFC1-ISBLANK = IF(NOT(ISBLANK(sum(FactActuals[Amount]))), SUM(FactForecast1[Amount])-sum(FactActuals[Amount]))
This prevents null values from showing in future but does not allow historical months to be treated as zero for the purposes of conditional formatting. In the linked sample file, April 2022 is a historical month that has no actuals but should be highlighted.
答案1
得分: 1
关于您的格式，尝试将您的度量更改为以下内容：
DiffToFC1 =
VAR d = SELECTEDVALUE('Date'[Year]) & SELECTEDVALUE('Date'[MonthNumber])
VAR t = YEAR(TODAY()) & MONTH(TODAY())
RETURN
IF(d<=t, SUM(FactForecast1[Amount])-SUM(FactActuals[Amount])+0)
希望这有帮助。
英文:
Regarding your formatting, try changing your measure to this
DiffToFC1 =
VAR d = SELECTEDVALUE('Date'[Year]) & SELECTEDVALUE('Date'[MonthNumber])
VAR t = YEAR(TODAY()) & MONTH(TODAY())
RETURN
IF(d<=t, SUM(FactForecast1[Amount])-sum(FactActuals[Amount])+0)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论