Forum Discussion
DateAdd Alternative for Function retrieving a Date, and Making a new Date
Hey Community!
I am getting an error from my calculated column when I try to filter it due to using DateAdd, how can I resolve this?
Column in Questions
Week LY = CALCULATE(
Sum(DayfileSummary[NetSales]),
DATEADD(DayfileSummary[DF_BusinessDay],-1,YEAR))
Feedback Type:
Frown (Error)
Timestamp:
2022-09-07T13:21:26.7914320Z
Local Time:
2022-09-07T07:21:26.7914320-06:00
Session ID:
ddb48463-8f74-4fbf-9f1e-03d7e0d1b5ed
Release:
April 2022
Product Version:
2.104.941.0 (22.04) (x86)
Error Message:
MdxScript(Model) (6, 21) Calculation error in measure 'DayfileSummary'[Week LY]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.
OS Version:
Microsoft Windows NT 10.0.17763.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 528049]
Peak Virtual Memory:
1.71 GB
Private Memory:
459 MB
Peak Working Set:
689 MB
IE Version:
11.1790.17763.0
User ID:
25db3681-a0a4-4acc-8351-11cdf00e505d
Workbook Package Info:
1* - en-US, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Snapshot Trace Logs:
C:\Users\administrator.PDCORP\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShote7f92045-e52e-4e33-9707-66437e26a6de.zip
Model Default Mode:
Import
Model Version:
PowerBI_V3
Performance Trace Logs:
C:\Users\administrator.PDCORP\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:
PBI_enableWebView2
PBI_mobileAuthoringFormattingUI
PBI_useModernFormatPane
PBI_sparklines
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_dataPointLassoSelect
PBI_compositeModelsOverAS
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_lineChartError
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
DPI Scale:
100%
Supported Services:
Power BI
Formulas:
section Section1;
shared DayfileSummary = let
Source = Sql.Databases("pdc-pos1\speedlink"),
SpeedlinkDB = Source{[Name="SpeedlinkDB"]}[Data],
dbo_DayfileSummary = SpeedlinkDB{[Schema="dbo",Item="DayfileSummary"]}[Data]
in
dbo_DayfileSummary;
shared ItemSummary = let
Source = Sql.Databases("pdc-pos1\speedlink"),
SpeedlinkDB = Source{[Name="SpeedlinkDB"]}[Data],
dbo_ItemSummary = SpeedlinkDB{[Schema="dbo",Item="ItemSummary"]}[Data]
in
dbo_ItemSummary;
shared Payments = let
Source = Sql.Databases("pdc-pos1\speedlink"),
SpeedlinkDB = Source{[Name="SpeedlinkDB"]}[Data],
dbo_Payments = SpeedlinkDB{[Schema="dbo",Item="Payments"]}[Data]
in
dbo_Payments;
shared AccountDetail = let
Source = Sql.Databases("pdc-pos1\speedlink"),
SpeedlinkDB = Source{[Name="SpeedlinkDB"]}[Data],
dbo_AccountDetail = SpeedlinkDB{[Schema="dbo",Item="AccountDetail"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_AccountDetail,{"TicketSeq", "SessionID", "TicketID", "ReverseTransID", "ForeignAmount", "TransType", "TransData", "GroupingTransID", "TenderGroupName"})
in
#"Removed Columns";
Hi NL-Nicolas,
This issue is caused by the first parameter in your DATEADD function. According to the syntax of DATEADD, you must provide a column that contains dates. And the dates argument can be any of the following:
1 A reference to a date/time column,
2 A table expression that returns a single column of date/time values,
3 A Boolean expression that defines a single-column table of date/time values.
For more details about the usage of DATEADD, please have a look at DATEADD function (DAX) - DAX | Microsoft Docs.
To solve this problem, you need make sure the first parameter is a date/time column by changing its date type or replace it with a date column from a Date/Calendar table. Here is a similar that may help you: Solved: DATEADD "expects a contiguous selection when the d... - Microsoft Power BI Community.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
5 Replies
Hi NL-Nicolas ,
Is the DayfileSummary[DF_BusinessDay] formatted as a date or a date/time?
If date/time, try changing the data type to a date in Power Query then try the calculation again.
So it is formatted as a date and time, the DateADD function is the issue in question but it is needed to get the projected week 😞
Hi NL-Nicolas,
This issue is caused by the first parameter in your DATEADD function. According to the syntax of DATEADD, you must provide a column that contains dates. And the dates argument can be any of the following:
1 A reference to a date/time column,
2 A table expression that returns a single column of date/time values,
3 A Boolean expression that defines a single-column table of date/time values.
For more details about the usage of DATEADD, please have a look at DATEADD function (DAX) - DAX | Microsoft Docs.
To solve this problem, you need make sure the first parameter is a date/time column by changing its date type or replace it with a date column from a Date/Calendar table. Here is a similar that may help you: Solved: DATEADD "expects a contiguous selection when the d... - Microsoft Power BI Community.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Giving the suggested form a read now, thank you so much for the help.
If I dont have a table with the column, can I use a relationship between the table with the dateadd to have just a date column in the table I am visualizing? Because the date doesnt exist anywhere, thats why I am using DateAdd. I am alternatively trying to make the Date in the server vs in PowerBi
