cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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
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...

image.png 

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Date arrithmatic in static calculated table

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

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Date arrithmatic in static calculated table

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.

 

Capture.PNG

 

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.

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 = 
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
)

 

Community Support Team
Community Support Team

Re: Date arrithmatic in static calculated table

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

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.

View solution in original post

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,919)