Forum Discussion
Helper IVsubtracting days from Date variable
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
Community ChampionJust 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.
Helper IVThanks for reply..
Negative days are being handled correctly when I do -27 I am getting 0 which is being not handled in DAX.
- AlexisOlson
Super User
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- siva_powerbi
solution is simple and working. Thanks
- edhans
What I was saying siva_powerbi is you were doing the -27 in the wrong space. You were effectively doing this:
DATE(YEAR, MONTH, DAY-27) and that was returning the error. I was saying do this:
DATE(YEAR, MONTH, DAY) - 27.See the difference?
I am glad the solution provided by AlexisOlson helped you out. I am trying to give you the why it worked. He is doing the full date then subtracting 27 from it as I suggested.