在Power Bi中将日期转换为”YYYY”格式

huangapple go评论121阅读模式
英文:

Converting Dates to "YYYY" format in Power Bi

问题

以下是翻译好的部分:

这些数据中的日期以数字格式存在,因此我可以追溯到前一年以计算“%增长”。
我尝试使用BI的DateAdd()ParallelDate(),但日期格式不正确。
请谁能帮我解决这个问题

我已经获得了每个国家的年份和互联网用户数量。尝试计算互联网用户的“%增长”,我需要使用DateAdd()ParallelDate()函数来追溯到前一年。
为了解决这个问题,我回到了Excel中的原始文档,并将日期从文本格式转换为“yyyy”格式的日期。上传了新的文档,但仍无法获得所需的结果。
BI公式如下:

Calculate(sumx( [USA],USA[互联网用户数], 
ParallelDate(USA[年份],-1, 年份))
英文:

the dates in this data are in number format so i can date back to previous year to calculate for say "% growth".
I tried using DateAdd() of BI and Parralleldate() but the dates are not in the right format.
Please who can help me sort out this problem

I have got the years and number of internet users for each country. Trying to calculate for a "% growth" of the internet users i need to date back to previous year using dateadd() or parralleldate() functions of BI.
To solve this, I went back to the original doc at Excel and formatted the dates from TEXT format to DATES "yyyy" format. Uploaded the new DOC and yet unable to get the desired result.
BI formular looks

Calculate(sumx( [USA],USA[no.internet users], 
Paralleldate(USA[Year],-1, YEAR))

答案1

得分: 0

对于日期函数(dateadd,Parallelperiod,Previousyear)的工作,您需要在数据模型中添加一个日历表。

由于您只有一个年份字段,添加一个计算列来创建一个日期字段,如 Date = DATE('USA'[Year], 1, 1),然后使用 Calendar = CALENDAR(FIRSTDATE('USA'[Date]), LASTDATE('USA'[Date])) 创建一个日历表,并在两个表之间创建一个关系。

现在您可以使用 CALCULATE([Sum], PARALLELPERIOD(Cal[Date], -1, YEAR)) 来获取上一年的数据。

英文:

For date functions (dateadd, Parallelperiod, Previousyear) to work you will need a Calender table in your data model.

As you only have a year field, add a calculated column to create a date field like Date = DATE('USA'[Year],1,1) and then create a Calender table using Calendar = CALENDAR(FIRSTDATE('USA'[Date]),LASTDATE('USA'[Date])) and create a relationship between both the tables.

Now you can use CALCULATE([Sum], PARALLELPERIOD(Cal[Date],-1,YEAR)) to get Previous year numbers.

答案2

得分: 0

给定你没有一个日历表,你可以创建一个类似这样的度量:

去年总额 = CALCULATE ( [总金额], FILTER(ALL('事实表名'), [年份] = MAX([年份])-1) )

基本上,我获取了当前年份(在这个上下文中,MAX([年份])只返回一个值),然后减去了一年。然后,这个度量基本上是为了将表达式的评估上下文调整回一年前。

如果你不仅有年份,我绝对建议使用一个日历表。(DAX中的许多与日期相关的函数都假定你有一个单独的日历表,所以如果遇到困难,要记住这一点。)

英文:

Given that you don't have a Calendar table you can create a measure like this:

Prev Year Total = CALCULATE ( [Total Amount],FILTER(ALL('FactTableName'),[Year] = MAX([Year])-1)
)

Basically, I'm getting current year (MAX([Year]) only returns one value in this context), and then subtracting one from it. Then the measure is basically adjusting the evaluation context for the expression back one year.

if you had more than just years, I would definitely suggest a Calendar table. (Lots of the date-related functions in DAX assume you have a separate calendar table, so keep that in mind if you get stuck.)

huangapple
  • 本文由 发表于 2023年6月29日 06:15:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/76577018.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定