DataSource.Error - enter value in a permitted format'

[mb0307]

Hi,

START DATE query below is being used as parameter to filter the data extracted from SAP BW.

let
    Today = Date.From(DateTime.FixedLocalNow()),

    Minus6MonthsFirstDayOfMonth = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-6)),
    Year6Months = Text.From(Date.Year(Minus6MonthsFirstDayOfMonth)),
    Month6Months = "0"&Text.From(Date.Month(Minus6MonthsFirstDayOfMonth)),
    Day6Months = "0"&Text.From(Date.Day(Minus6MonthsFirstDayOfMonth)),
    CreateTextForQueryStart = Text.End(Day6Months,2) &"/"& Text.End(Month6Months,2) &"/"& Year6Months

in
    CreateTextForQueryStart

and in the main SAP extraction query, START DATE parameter is being used like this:

 {Cube.ApplyParameter, "[!V000018]", {#"Start Date", #"End Date"}},

It was all working fine but since this morning it started to throw this error:

DataSource.Error: The SAP BW server reported an error: 'For characteristic 0DSDEL_DATE, enter value in a permitted format'. To find more information about this error, visit the SAP support site and search for 'BRAIN 599'.
Details:
    DataSourceKind=SapBusinessWarehouse
    DataSourcePath=usa-bw4-01/00/100
    ID=BRAIN
    NUMBER=599
    MESSAGE_V1=0DSDEL_DATE
    MESSAGE_V2=15/01/2021
    SYSTEM=BLPCLNT100
    Command=SELECT
    {
        [Measures].[00O2TNTRG745UYK7Z5GU1UJ8N],
        [Measures].[00O2TNTRG745RUP66ISOKJ0HI],
        [Measures].[00O2TNTRG747L8XHPR7RCH5TX],
        [Measures].[00O2TNTRG745RUP66ISOKIHIU]
    }ON 0,
    NON EMPTY CROSSJOIN(
        CROSSJOIN(
            CROSSJOIN(
                CROSSJOIN(
                    CROSSJOIN(
                        CROSSJOIN(
             
                                                     ...

I tried following:

• Change START DATE format to US and UK - no success
• My computer regional setting set to UK
• Power BI regional setting set to UK
• SAP user profile setting set to UK

But still no success.

Help will be much appreciated.

[mahoneypat · Accepted solution]

I'm guessing it is related to the day going to two digits (10) and your step where it adds "0" to it.  Your last step should address that, but here is a simpler way to get to your desired format with Date.ToText.

let

Minus6MonthsFirstDayOfMonth = Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.FixedLocalNow()),-6)),
Custom1 = Date.ToText(Minus6MonthsFirstDayOfMonth, "MM/dd/yyyy")
in
Custom1

Pat

[mb0307]

@mahoneypat  Thanks that worked.