Like for Like Comparison Across Years when the current year is not a full year

Hi

This has got to be so common and so simple, but blow me, I can’t figure it out.

How do we get a like for like comparison across multiple years (ie more than the previous year), when the current year is only part way through and I don’t have dates selected. I just want the summary totals.

So, say we are talking about this year, I just want to see January sales across the years.

Any and all guidance would be great! Thanks Erica

HI @Ericadyson. On first read, I imagine using a DATEADD for each desired prior period (minus years) along with the current year date period of interest will be the solution, but if you could please post a sample work-in-progress PBIX and an Excel mock-up of your desired outcome, that’d help direct the forum members.
Greg

Hi @Ericadyson, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi Greg… thanks for reaching out. I don’t think it’s DATEADD and year because it needs to be dynamic - not hard coded for a given period. So I would get the corresponding figures across years, whatever the last month of actual sales happened to be. I was looking for one measure that would do the trick.

I played around with a month offset and saying that total sales would equal the total sales where the max month = month offset. That seemed to do it, but wasn’t sure if that was optimum or if I was tackling the issue well.

I then have a table - month as row and total sales (this new measures version) as value. The month would be whatever the last month was. Is my thinking and measure OK?

Sales Compare =
VAR maxmonth= CALCULATE(MAX(Dates[MonthOfYear]),ALL(Dates),Dates[MonthOffset]=0)
return
CALCULATE([Total Sales],FILTER(dates,Dates[MonthOfYear]=maxmonth))

If you can’t do a PBIX, how about a sample dataset in Excel and a couple of mockups of what you’d like to see with specific slicer selections?
Greg

Hi Greg - I thought I had replied but obviously not. Thanks for reaching out (again). I don’t think DATEADD will do the trick because I wanted it to be dynamic… ie it will always give me a comparison up to whatever is the last month in the current year. I managed to figure something out but don’t know if it’s optimum.

This is what I came up with
Sales Compare Cumul =
VAR maxmonth= CALCULATE(MAX(Dates[MonthOfYear]),ALL(Dates),Dates[MonthOffset]=0)
return
//calculates cumul total across parallel periods whatever the month offset happens to be. the month offset =0 is this month

[Uploading: Advanced Data Transformation and Modeling perpetual data.pbix…]

See page 2. _ I’m using one of Sam’s files and Melissa’s perpetual data refresh (it’s brilliant)!

Thanks again for taking an interest and helping out. Erica

Hi @Ericadyson

Hopefully I’m understanding the requirement.

In my date table I would have a calculated column which would be as per below. So I’ll say I have a Sales table and a date table.

DateWithSales = ‘Date’{Date} <= MAX ( Sales [Order Date])

This will show true for dates which have actual sales in.

Then create the below measure

ShowValueForDates =
VAR LastDateWithData =
CALCULATE (
MAX ( ‘Sales’ [Order Date] ,
REMOVEFILTERS ()
)
var FIrstDateVisible =
MIN ( ‘Date’ [Date] )
VAR Result =
FirstDateVisible <= LastDateWithData
RETURN
Result

For the measure that willl give you a comparison against the previous year BUT ONLY up to the same date like 19/01/2021 and 19/01/2020. Say you had a Sales Amount Measure you wanted to compare with a previous year.

Sales PY =
IF (
[ShowValueForDates],
CALCULATE (
[Sales Amount],
CALCULATETABLE (
DATEADD ( ‘Date’ [Date], -1, YEAR ),
‘Date’ [DateWithSales] = TRUE
)
)
)

If you want to look 2 years previous then change the - 1 to - 2.

Hopefully this is what you are after.

DJ

Hi @Ericadyson , did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Ericadyson, we’ve noticed that no response has been received from you since the 19th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi… sorry for not getting back to you. Yes, that’s great. It seems so easy when you know how! Thanks a lot. Really appreciated it.

My attempt had the data up to the latest month but that does assume that there are sales in that month and there might not be. So your measure is great - elegant and what’s more … I can understand it :-). Thanks for taking the time to reach out.

1 Like