Best way to add a day and get Visualization to work?

[Jackofall]

I am working on moving a complex SSRS-report to BI.

I have a column of dates that I want to add a day to to compensate for UTC vs my timezone. When I use New Measure = Dateadd('table'[date];1;DAY), I can't use that in any Visualization. I get this Error message.

Dateadd 1 day from working dates.
I also tried to use Replace Values in the Query Editor that looked more like CTRL-H to me. I am completely dumb founded why I cant use my measure that I added one day to.

I also noticed one thing in Visualization that really confuses me. My DataSet include dates between 2017-08-31 and 2018-04-30. When I present that column in  a Table I get dates from 2017-01-01 to 2018-12-31. I Expect the table to show the rows in my DataSet. What is happening?

There is a lot I like about PBI but there is also a lot of aggro when I draw on my experience with Excel, T-SQL and Report Builder.

[MFelix · Accepted solution]

Hi @Jackofall,

On DAX measures are calculated based in.context so they are calculated each time you add them tona visual based on the information is in the report, visual, slicers, .... based on this you can't use a column for your messure without an aggregator that allows you to know what to do with all the line in your information


In this case you should use something like MAX when refering to the column so.you would get something like this

Newm measure = DATEADD ( MAX(Table[Date]) ; 1 ; Day)

However I.don't believe this would return the expected result you need you need to make a calculated column on your model using the formula as you have and then use that new column on your.visuals and measures instead of the previous date column.

Regards
MFelix

[MFelix · Accepted solution]

Hi @Jackofall,

On DAX measures are calculated based in.context so they are calculated each time you add them tona visual based on the information is in the report, visual, slicers, .... based on this you can't use a column for your messure without an aggregator that allows you to know what to do with all the line in your information


In this case you should use something like MAX when refering to the column so.you would get something like this

Newm measure = DATEADD ( MAX(Table[Date]) ; 1 ; Day)

However I.don't believe this would return the expected result you need you need to make a calculated column on your model using the formula as you have and then use that new column on your.visuals and measures instead of the previous date column.

Regards
MFelix

[Jackofall · Accepted solution]

Thanks you. I had the wrong expectation. In the Query Editor I create a Custom Column that I expect will solve my problem.

I used M to fix this. Date.AddDays([datecolumn],1) The date now show up correctly. Thanks a bunch for pointing me in the right direction @MFelix.