Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[willie sczech]

I have SQL database for a legacy system (100M records) where the date is stored as a text number that is the excel date -2. I’d like to create a parameter that allows the user to enter an normal data as “2/4/18” and have PBI convert to the it’s numeric value -2. Any help would be welcome.

——————————
willie sczech
management analyst
tdlr
austin TX
5124634147
——————————

[Audrey Abbey]

Can you show an example of the value you would expect for 2/4/2018?
Not quite following what you want to do. 

——————————
Audrey Abbey
SR. BI Developer/Analyst
New Seasons Market
Portland OR
——————————
——————————————-

[willie sczech]

7/28/2013 = 41483 in excel but = 41481 in legacy db.

——————————
willie sczech
management analyst
tdlr
austin TX
5124634147
——————————
——————————————-

[Guy Shoemaker]

?I would try datediff (1/1/1900, parameter, day) – 1 as your formula.  You can look up the definition of DateDiff online.  You do the minus 1 because it is the number of days between your start & end date.  So if you do 1/1/1900 (the base date for Excel) datediff will return 0 but Excel treats 1/1/1900 as 1.  So datediff returns a value already 1 less than the Excel value & subtracting 1 again gets you to your legacy offset of 2.

——————————
Guy Shoemaker
Application Administrator
International Capital & Management Company
St. Thomas
340-715-5820
——————————
——————————————-