Forum Discussion
Modfiy Date field
Hi All,
I have a CSV source with 2 comma seperated columns, those are called "Date" and "Time". This is an example:
"7-3-2020 23:00:00","@{Hours=19; Minutes=42; Seconds=0; Milliseconds=0; Ticks=709200000000; Days=0; TotalDays=0,8208333333333333; TotalHours=19,7; TotalMilliseconds=70920000; TotalMinutes=1182; TotalSeconds=70920}",
There are a few things that have to be modified here. First thing is that the date is incorrect, the real date is always 1 day later. I think this has something to do with the time zone I'm in. Because when you add 1 hour the correct date will be displayed. But as I have no control over the source, I would have to modify this in PowerBI. It's in D-M-YYYY format, so in this case the result should become 8-3-2020. Next thing is that the time is always 23:00:00. This is also incorrect. The real time is in the next column, so in this case that is 19:42. HH:mm format is good enough for me here. Seconds and Milliseconds are always 0 in the CSV, so I don't need that in my end result.
How can I end up with a correct date/time, in this case 8-3-2020 19:42.
I'm guessing that a meassure is the best way to achieve this, but I do not have enough knowledge in that area yet...
4 Replies
Hey Worlock
First off, if the time in the first column is wrong and unecessary, go into query editor and convert the column to date instead of date time format.
Then, all you need to get the correct date is created a calculated columns with the DATEADD function: https://docs.microsoft.com/en-us/dax/dateadd-function-dax
Correct Day = DATEADD(Table[date], 1, Day)
Then check out this thread on combining date & time: https://community.powerbi.com/t5/Desktop/Unify-Date-and-Time-columns-into-one-DateTime-column/td-p/25820
It is possible though not always recommend. If you still want to do it read this thread: https://community.powerbi.com/t5/Desktop/data-and-time-merge-issue/td-p/140322
There is an option "Combine Date and Time" as stated in the above thread.
This is the correct way to do it with a measure if you so choose:
If this helps please kudo.
If this answers your question please accept it as a solution.
For some reason my reply isn't posted. This happens to me all the time when I'm nog logged in. But I am still able to hit the "Post" button? Anyway, I'll try it again:
I am already failing in the first step, I try to create a meassure with this code:
Correct Day = DATEADD(Tankbeurten[ConsumptionDate], 1, Day)'Tankbeurten' is the name of my Table, 'ConsumptionDate' is the name of the date column.Power BI changes the first comma in a ; sign. So:Correct Day = DATEADD(Tankbeurten[ConsumptionDate]; 1, Day)And I get this error:The syntax for 'Day' is incorrect. (DAX(DATEADD(Tankbeurten[ConsumptionDate], 1. Day))).The only way I get Power BI to accept my syntax, is by replacing al , to ;Correct Day = DATEADD(Tankbeurten[ConsumptionDate];1;day)But then I get no data out of it.Error Message:
MdxScript(Model) (4, 35) Calculation error in measure 'Tankbeurten'[Correct Day]: A table of multiple values was supplied where a single value was expected.Thanks for your help!Hi Worlock
I can reproduce this error with a measure.
as a workaround, It would work with a calculated column.
It seems it is impossible to use "DateAdd" in that way for a measure.
You could use "DateAdd" in a measure in ways below:
Measure = CALCULATE(COUNT('Table'[Date]),DATEADD('Table'[Date],1,DAY))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Worlock
I would value Tad17's answer, if this doesn't slove your problem, please feel free to let me know.
Best Regards
Maggie
