Forum Discussion

siva_powerbi's avatar
siva_powerbi
Helper IV
5 years ago
Solved

subtracting days from Date variable

I am trying to subtract days from date varible that I have calculated in formula but I am getting the error.

 

An argument of function 'DATE' has the wrong data type or the result is too large or too small.

 

Code:

 

Column 3 =
Var firstperiod = "12/28/2020 to 01/24/2021"
Var StartDate = DATE(YEAR(DATEVALUE(RIGHT(firstperiod,10))),MONTH(DATEVALUE(RIGHT(firstperiod,10))),DAY(DATEVALUE(RIGHT(firstperiod,10))))
Var FP_ED = StartDate
Var FP_SD = DATE(YEAR(StartDate),MONTH(StartDate),DAY(StartDate)-27)
Var SP_SD = DATE(YEAR(FP_SD),MONTH(FP_SD),DAY(FP_SD)-1)
Var SP_ED = DATE(YEAR(SP_SD),MONTH(SP_SD),DAY(SP_SD)-27)
Return
SP_ED
 
If I get 0 as the subtracted result of the day then I would expect to get the last day of the previous month, in above example
 
SP_ED will be decoded to DATE(2020,12,0) in this case I need to get 11/30/2020
 
Any help is appretiated
 
Thanks
Need Help
Show and Tell
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    Zero has the same problem as negative numbers in that there is no zeroth day of the month.

     

    Your column definition is simpler as follows:

    Column 3 =
VAR firstperiod = "12/28/2020 to 01/24/2021"
VAR StartDate = DATEVALUE ( RIGHT ( firstperiod, 10 ) )
VAR FP_ED = StartDate
VAR FP_SD = StartDate - 27
VAR SP_SD = FP_SD - 1
VAR SP_ED = SP_SD - 27
RETURN
    SP_ED

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    Just subtract from the date, not the day. So Date(2021,1,1) - 1 will return Dec 31, 2020.

    What I suspect is happening is where  you are doing -27 it is returning a negative day which messes up the DATE() function. Just calc the date, then add/subtract days. In this case DAX is just like Excel.

    • siva_powerbi's avatar
      siva_powerbi
      Helper IV
      5 years ago

      Thanks for reply..

       

      Negative days are being handled correctly when I do -27 I am getting 0 which is being not handled in DAX.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        5 years ago

        Zero has the same problem as negative numbers in that there is no zeroth day of the month.

         

        Your column definition is simpler as follows:

        Column 3 =
VAR firstperiod = "12/28/2020 to 01/24/2021"
VAR StartDate = DATEVALUE ( RIGHT ( firstperiod, 10 ) )
VAR FP_ED = StartDate
VAR FP_SD = StartDate - 27
VAR SP_SD = FP_SD - 1
VAR SP_ED = SP_SD - 27
RETURN
    SP_ED