Forum Discussion

Worlock's avatar
Worlock
New Member
6 years ago

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=...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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:

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Re-Combine-Date-and-time-column-into-datetime-column-in/td-p/822489

 

If this helps please kudo.

If this answers your question please accept it as a solution.

 

 

Worlock's avatar
Worlock
New Member
6 years ago

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!

 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.