I have YTD data in a table and want to compare it to the same YTD data from the previous year
For eg. Current Year data is from 1st Jan 2018 to 31st Oct 2018 (10mths). I want to compare this with the Previous years data from the same periods ie 1st Jan 2017 to 31st Oct 2017
The following works except for when I drill down into the data based on Customer. I'm assuming this fails because the chosen customer does not have sales for every month
=CALCULATE(SUM(Table1[Sales]),
DATESBETWEEN(Dates[Date],FIRSTDATE(DATEADD(Dates[Date],-12,MONTH)),
LASTDATE(DATEADD(Sales[TransDate],-12,MONTH))
How can I achieve the required analysis with data that is not contiguous for a given customer eg sales in Jan, Mar, Apr, Jun, July, Aug, Sept, Oct
Hi Alison,
It may be something that you should use PARALLELPERIOD for, but without knowing the context I can't be sure.
Please provide a screenshot of the PivotTable layout you want to see this calculation in and showing the incorrect results, so we can understand the context required of the measure.
If you can also upload a sample Excel file then that will get you a more accurate answer more quickly.
Mynda
P.S. As this is a DAX question I've moved it to the Power Pivot course forum.
Table1 linked to Date Table via TransDate
Customer Name Sales TransDate
ABCD 1000 2/1/2017
ABCD 500 3/1/2017
ABCD 250 5/2/2017
XYZ 1500 8/3/2017 <==== no March date for customer ABCD
ABCD 1000 30/4/2017
ABCD 500 4/5/2017
XYZ 25 6/6/2017
ABCD 500 1/1/2018
ABCD 1000 5/2/2018
XYZ 500 15/3/18
ABCD 750 6/3/2018
ABCD 50 28/4/2018
DESIRED RESULT
The formula quoted above does give this result
Sales Jan to April
2017 = 4750
2018 = 2800
If I try to look at 2017 vs 2018 by customer
ABCD 2017 = error - unable to calculate as expecting contiguous date
ABCD 2018 = 2300
Hi Alison,
You can use PARALLELPERIOD or SAMEPERIODLASTYEAR for this. See file attached.
Mynda
Thank you 🙂
