Forum Discussion
DATESBETWEEN Date and Time Field
Community ChampionDAX is a very powerful language. It has an ability to morph in ways you wouldn't normally expect based on other software. If you create a new column, subtract the end column from the start column, then format this column as a decimal number, you will get a fraction of a full day worked.
Another example. If you have 2 columns of integers Table[Int1] and Table[Int2], the following formula will work
Table[Int1] & Table[Int2]*2
DAX will multiply column 2 by 2, then append the result via concatenation to the back of column 1. Not normally you would normally expect, but very powerful.
Now another thing is that I would not be using a calculated column for your example. It is very common for Excel users to do this, but is is 'normally' not the best way (normally based on my experience working with Excel users migrating to Power Pivot). You should consider using a Measure for your calcuation.
Microsoft EmployeeHi
I created the below new measure
PreviousYearAmount = CALCULATE(SUM[TaxAmt], DATEADD([Invoice Date],-1,YEAR))
And I get the below error
Calculation error in measure: A Date column with duplicate dates specified in the call function 'DATEADD'. This is not supported.
How do I resolve this?
Many invoices can be generated on a single date.
So in the filters provided, if year=2015 and month= Dec , my visual should comparison of Dec-15 amount with Dec-14.
if year=2014 and month= Dec then visual should comparison of Dec-13 and Dec-14.
- MattAllington
The first parameter of DATEADD should be the column in your Calendar table. It looks like you are using a measure or a column in your fact table
- aksh
Hi
Sorry, I didn't get your message.
I want to know if I am creating a new measure (not a new column) can't I use a date column that has multiple duplicate values?
What is the resolution to this problem?
SeanTime Intelligence Functions which DATEDD is one of - require a Date/Calendar table in your Data Model.
Go to Matt's link here and create one
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
Then change your formula
PY Amount = CALCULATE(SUM([TaxAmt]), DATEADD(CalendarTableName[DateColumn], -1, YEAR) )