cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jackofall
Resolver I
Resolver I

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

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.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.

 

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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.

 

 

View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors