英文:
Add last date column of the month based on local date in power BI
问题
我想要添加每月最后工作日的列（在这种情况下，我想要创建下面标记为黄色的列last date）。因为每个国家的节假日安排不同，所以不能使用公式来排除星期日。
例如，在下面的图片中，四月份的最后一天是28-Apr-2023（而不是30-April-2023），函数EOMONTH(Data[Date], 0 )在这种情况下也不能使用。五月份也是类似情况...
有人有解决办法吗？请帮助我。
英文:
I want to add the column of the last working day of the month (in this case, I want to create the column last date highlighted in yellow bellow). Because the holiday schedule of each country is different, so it is not possible to use the formula to exclude Sunday.
For example in the image below me, my last day of April is 28-Apr-2023 (Not 30-April-2023), the function EOMONTH(Data[Date], 0 ) can't use in this case too. Similar to May...
Does anyone have any solution please help me?
答案1
得分: 1
以下是翻译好的部分：
"要给出确切答案需要查看您的数据结构，但基本思路是执行一个排除非工作日的LASTDATE()计算。这假定您有一个 'Calendar'[EOMONTH] 列（EOMONTH('Calendar'[Date],0)）。
CALCULATE(
LASTDATE( 'Calendar'[Date] ),
ALLEXCEPT( 'Calendar', 'Calendar'[EOMONTH] ),
WEEKDAY('Calendar'[Date],3) < 5
)
这假定标准周末为星期六/星期日。如果要根据区域设置更改周末，您需要一个与日历维度和区域维度相关联的周末事实表。同样的结构也用于具有区域特定假日模式。"
英文:
It's hard to give an exact answer without seeing your data structure, but the basic idea is to perform a LASTDATE() calculation filtered to exclude non-business days. This assumes you have a 'Calendar'[EOMONTH]' column (EOMONTH('Calendar'[Date],0)).
CALCULATE(
LASTDATE( 'Calendar'[Date] ),
ALLEXCEPT( 'Calendar', 'Calendar'[EOMONTH] ),
WEEKDAY('Calendar'[Date],3) < 5
)
This assumes a standard weekend of Saturday/Sunday. To change that by locale, you would need a weekend fact table related to the Calendar dimension and a locale dimension. You would need the same structure to have a locale-specific holiday schema.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论