Date arrithmatic in static calculated table

[RobertSlattery]

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
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 2000), "Order Number", [Value]),
            "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)

[v-piga-msft]

Hi @RobertSlattery,

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
ADDCOLUMNS(
    FILTER(
        ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 2000), "Order Number", [Value]),
            "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

[RobertSlattery]

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 = 
ADDCOLUMNS(
    ADDCOLUMNS(CALENDAR([From Date], [To 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(
    ADDCOLUMNS(
        FILTER(
            ADDCOLUMNS(SELECTCOLUMNS(GENERATESERIES(1000, 10000), "Order Number", [Value]),
                "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
)

[v-piga-msft · Accepted solution]

Hi @RobertSlattery,

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