DateAdd Alternative for Function retrieving a Date, and Making a new Date

[NL-Nicolas]

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";

[v-cazheng-msft · Accepted solution]

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

[NL-Nicolas]

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

[NL-Nicolas]

Giving the suggested form a read now, thank you so much for the help. 

[Adescrit]

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.

[NL-Nicolas]

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 😞