Problem with DATEADD

DAX DAX Calculations
1

Why Quantity sold LY same day option 1 is working and option2 not . Option 2 is showing 216 for all rows.
Please suggest
Reference PowerBI File Name : Ultimate Beginners Guide to DAX examples.pbix
//option 1
CALCULATE(
[Total Quantity Sold],
FILTER(
ALL(Dates[Date]),
Dates[Date]=MIN(Dates[Date])-365 )

//Option2
CALCULATE(
[Total Quantity Sold],
FILTER(
ALL(Dates[Date]),
Dates[Date]=DATEADD(Dates[Date],-1,YEAR )

))
Thanks,
Harry

2

Hi HarsimranjeetSinh,
Try the Second option again but remove the Filter(All (Date[Date]), try this formula
Calculate(
[Total Quantity Sold], DATEADD (Date[Date], -1, YEAR))
By using All in the formula, it will remove all filters and it will compare it with the very first year, ignoring the initial context.
Hope this helps.
Cheers!

3

Hi @Promisegodbold,

Thanks for your reply.

All removing all filters makes sense.
can you please explain comparing it with very first year and ignoring initial context
Just for example if the Date cell is 01/01/2016 than what range it take.
as its returning 216 as value for each row and its confusing me what range it is considering.

Thanks,
Harry

4

This is all to do with the context you have placed DATEADD in with the FILTER function.

Review this tutorial here to see how to use DATEADD properly.

Sam

5

@sam.mckay,

I tried reading that couple of times but i’m not able to correlate how 216 is coming.
Can you please elaborate same.
I tried evaluating expression in dax studio but its giving me 2017 year from the filter table

QuntySldLY
QuntySldLY.png1218×603 30.8 KB

Thanks,
Harry

6

@HarsimranjeetSingh
Is your date table marked as such?

How%20to%20Mark%20as%20Date%20Table
How to Mark as Date Table.png1180×650 34.4 KB

If it’s not, the time intelligence functions could not work as you expected.

7

@Nick_M,

I tried that but it’s same for Option 2 syntax i.e. 216 for each row.

Thanks,
Harry

8

image

9

@Nick_M
Ok that make sense. I’ve check Date table its been constructed from 01/01/2015 to 12/29/2017.
so 216 are for rows of sales onward 12/30/2017.

But one thing i’m left uncleared is how come below line of code using that date range

Dates[Date]=DATEADD(Dates[Date],-1,YEAR )

Also correct me if i’m wrong w.r.t below expression Left part is value is value of Table generated by ALL(DATE) and right part MIN(Dates[Date])-365 is value of cell value used in visual.

Dates[Date]=MIN(Dates[Date])-365

Thanks,
Harry

10

The main issue in my eyes is that you’re using the DATEADD function incorrectly. I would really recommend watching the video because you don’t need to place the DATEADD function within a FILTER function and I wouldn’t recommend it.

This is a time intelligence function which can be simply placed as a parameter in the CALCULATE function.

Sam