cancel
Showing results for
Did you mean:
Highlighted
RobertSlattery Member

## Date arrithmatic in static calculated table

Can anyone help me understand why this throws an error?

```Orders =
VAR startDate = DATE(2017, 7, 1)
VAR endDate = DATE(2018,4,30)
VAR invLag = 30
VAR prob = 0.8
RETURN
FILTER(
"Date", RANDBETWEEN(startDate, endDate),
"Value", RANDBETWEEN(0, 1000)
),
WEEKDAY([Date],2) < 6
),
"Week Day", FORMAT([Date], "ddd"),
"Inv Date",
VAR dif = DATEDIFF([Date], endDate, DAY)
VAR dif0 = IF(dif > invLag, 0, dif)
VAR threshold = prob - dif0 * prob / invLag
RETURN
IF(RAND()> threshold, BLANK(), INT([Date] + RANDBETWEEN(0, invLag)))
)```

Its a stand-alone calculated table.  Just do Modelling/New Table and paste this in.

The error seems to be ascociated with confusion over implicit type-casting between Date and Int.

The error is... I think the problem line is with this line: VAR dif0 = IF(dif > invLag, 0, dif)

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

## Re: Date arrithmatic in static calculated table

By my research, the problem line should be  with this line: VAR dif0 = IF(dif > invLag, 0, dif);

If we repalce dif with DATEDIFF([Date], endDate, DAY) , it will work.

However, if we define the var dif and use the formula VAR dif0 = IF(dif > invLag, 0, dif), it will get error.

This is a issue has reported to the Product Team: CRI 64579879 and will be fixed in May release.

Here is a similar thread you could have a reference.

Best Reagards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3 Community Support Team

## Re: Date arrithmatic in static calculated table

You're right, that problem line is with this line: VAR dif0 = IF(dif > invLag, 0, dif).

For the workaround, I suggest you could create the table with the formula below first.

```Orders =
VAR startDate = DATE(2017, 7, 1)
VAR endDate = DATE(2018,4,30)
VAR invLag = 30
VAR prob = 0.8
RETURN
FILTER(
"Date", RANDBETWEEN(startDate, endDate),
"Value", RANDBETWEEN(0, 1000)
),
WEEKDAY([Date],2) < 6
),
"Week Day", FORMAT([Date], "ddd")

)```

Then create the calculated column.

```Diff =
VAR startDate =
DATE ( 2017, 7, 1 )
VAR endDate =
DATE ( 2018, 4, 30 )
VAR invLag = 30
VAR prob = 0.8
VAR dif =
DATEDIFF ( [Date], endDate, DAY )
VAR dif0 =
IF ( dif > invLag, 0, dif )
VAR threshold = prob
- dif0 * prob
/ invLag
RETURN
IF ( RAND () > threshold, BLANK (), INT ( [Date] + RANDBETWEEN ( 0, invLag ) ) ) ```

And you will get the table. Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RobertSlattery Member

## Re: Date arrithmatic in static calculated table

Hi Cherry (@v-piga-msft), thanks for the answer but, pleas please PLEASE include an explanaition of the root cause so that we don't have to randomly stumble arround in the dark trying various permutations all the time.

I managed to work arround the problem by adding a column to my date table and then using a lookup in the fact table to access the variable weighting...

```Dim Date =
"Year", YEAR([Date]),
"Date Key", VALUE(FORMAT([Date], "YYYYMMDD")),
"FY",
VAR m = MONTH([Date])
VAR y = YEAR([Date])
RETURN IF(m > 6, y + 1, y),
"DOW", FORMAT([Date], "dddd")
),
"Weignt",
VAR endDate = [To Date]
VAR invLag = [invLag]
VAR prob = 0.8
VAR d = DATEDIFF([Date], endDate, DAY)
VAR x = IF(d > invLag, invLag, d)
RETURN
prob - x * prob / invLag
)```
```Orders =
VAR startDate = DATE(2017, 7, 1)
VAR endDate = [To Date]
VAR invLag = [invLag]
RETURN
FILTER(
FILTER(
"Date", RANDBETWEEN(startDate, endDate),
"Value", RANDBETWEEN(0, 1000)
),
WEEKDAY([Date],2) < 6
),
"Week Day", FORMAT([Date], "ddd"),
"Inv Date",
VAR d = IF(RAND() < LOOKUPVALUE('Dim Date'[Weignt], 'Dim Date'[Date], [Date]), BLANK(), INT([Date] + RANDBETWEEN(0, invLag)))
RETURN d,
"Weight", LOOKUPVALUE('Dim Date'[Weignt], 'Dim Date'[Date], [Date])
),
WEEKDAY([Inv Date]) < 6
)``` Community Support Team

## Re: Date arrithmatic in static calculated table

By my research, the problem line should be  with this line: VAR dif0 = IF(dif > invLag, 0, dif);

If we repalce dif with DATEDIFF([Date], endDate, DAY) , it will work.

However, if we define the var dif and use the formula VAR dif0 = IF(dif > invLag, 0, dif), it will get error.

This is a issue has reported to the Product Team: CRI 64579879 and will be fixed in May release.

Here is a similar thread you could have a reference.

Best Reagards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 