cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav Established Member
Established Member

DateAdd function in column not working

Hi,

 

I was trying answer another thread and hit a problem on DateAdd function.

I was trying to calculate previous month sales amount for a given sales date. I have a Date table and created 1:many between Date and Sales table.

 

Original Data:

CompnayDateAmountPrev Month
A01/01/201810 
B01/01/201820 
A01/02/20183001/01/2018
B01/02/20184001/01/2018
A01/03/20185001/02/2018
B01/03/201860

01/02/2018

 

Intended Results:

CompnayDateAmountPrev Month 
A01/01/201810  
B01/01/201820  
A01/02/20183001/01/201810
B01/02/20184001/01/201820
A01/03/20185001/02/201830
B01/03/20186001/02/201840

 

I created a column as below which works:

Prev Month Sales1 =
CALCULATE(

                    MIN(Sheet1[Amount]),

                    FILTER(ALL(Sheet1),

                               Sheet1[Date] = EARLIER(Sheet1[Prev Month]) && Sheet1[Compnay] = EARLIER(Sheet1[Compnay])

                     )

)

 

But when I tried to use DateAdd function it gives me blank.

Prev Month Sales2 =
CALCULATE(MIN(Sheet1[Amount]), DATEADD(Sheet1[Date], -1, MONTH))

 

Why is the DateAdd not working in either as a column or a measure?

 

When I use DateAdd just in a column I get the date in date/time format.

Prev Month From DateAdd =
CALCULATE(DATEADD(Sheet1[Date], -1, MONTH))

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DateAdd function in column not working

Hi @anandav,

 

This is a question of context, if you remove the previous month from your table view it will give you the calculation you need.

 

Be aware that the measure are very sensitive in terms of context and if you don't place a certain column in your measure, when you add that measure with that column the value will be off.

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
Super User
Super User

Re: DateAdd function in column not working

Hi @anandav,

 

Why are you trying to create a calculated column with previous month to calculate the total sales of previous month?

 

The best way is to have a calculated measure using the DATEADD or similar.

 

Your measure should look something like this:

 

Prev Month Sale = 
CALCULATE(SUM(Sales[Amount]); DATEADD(Sales[Date]; -1; MONTH))

Beware that using the DAX formula on columns is different from measures, since the context is given in different way, that's why the Prev. Month sales 1 is working and the second one is not since you are taking context from your DAX formula and the calculation is incorrect.

 

Regards,

MFelix



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

Proud to be a Datanaut!




anandav Established Member
Established Member

Re: DateAdd function in column not working

Hi @MFelix,

That is exactly what I am strugling with - I have used DateAdd in Prev Month Sale2 and it is just giving me blank. Whether I do it as a column or measure it is blank.

 

Super User
Super User

Re: DateAdd function in column not working

Hi @anandav,

Since you are using a dates table with a relationship to the sales date in the DATEADD function you should use the dates table column and not the column from the sales table.

In my tests with your data I didn't made a date table that's why ot worked whit that column.

Regards,
MFelix


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

Proud to be a Datanaut!




anandav Established Member
Established Member

Re: DateAdd function in column not working

Hi @MFelix,

 

I have uploaded the pbix file here.

 

I am not using Date table and still it does not work. Could you please check the file to see what I am doing wrong?

Thank you for your patience regarding this problem.

 

Super User
Super User

Re: DateAdd function in column not working

Hi @anandav,

 

This is a question of context, if you remove the previous month from your table view it will give you the calculation you need.

 

Be aware that the measure are very sensitive in terms of context and if you don't place a certain column in your measure, when you add that measure with that column the value will be off.

 

Regards,

MFelix



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

Proud to be a Datanaut!




View solution in original post

anandav Established Member
Established Member

Re: DateAdd function in column not working

Hi @MFelix,

 

Thanks a lot for the help. Yes, that works in the measure.

I've been strugking with this for 2 days now. Smiley Happy

 

But the same formula does not work in a column. How do I use DateAdd in a column to get previous month sales?

Super User
Super User

Re: DateAdd function in column not working

Hi @anandav,

 

I don't understand why you want to create a column previou month value, this is the type of information you should use as measure. 

 

As a DAX best practice if you can calculate a value as measure don't create a calculated column.

 

The use of the DAX formulas is not the same in a calculated measured or in a column, this as to do with context. So you must be carefull in the way you make the use of the formulas.

 

Looking at the calculations you are using on the column the formula you use with the earlier works correctly since the column uses the line context so it looks at a single line and get the earlier value (previous line), when you use the DATEADD the context is different and you are trying to find in all your table what is the previous month lines and then return their value. Chekc also the DATEADD formula documentation because it also show some limitations like the fact that the result table includes only dates that exist in the dates column.

 

Can you please explain why do you want to use a column instead of a measure?

 

Regards,

MFelix



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

Proud to be a Datanaut!




anandav Established Member
Established Member

Re: DateAdd function in column not working

Hi @MFelix,

 

The need to get DateAdd work in column is just for my education purpose to uderstand the functionality in measure vs. column.

 

Thank you for your detail reply.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 204 members 2,327 guests
Please welcome our newest community members: