year on year for a product group

[PaulMarston]

I have used the year on year calculation for a date or month but I would like to use this for a product group but I can't see how 

[Greg_Deckler]

Please post sample data and expected output.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[PaulMarston]

Hi Greg thanks for answering my post

I’m using this calculation to get the sales value for last year

Sales Last Year = CALCULATE([Sales This Year],SAMEPERIODLASTYEAR(Sales[Date]))

I can then see (which is correct)

Month                  Sales Last Year

Jan                        10,000

Feb                        11,150

Mar                       12,300

But now I want to see the sales for last year by a product group

Pork                      8,000

Beef                      9,000

Lamb                    7,000

Chicken                9,450

What calculation do I need for this?

[Greg_Deckler]

@PaulMarstonwhat I really need is some example source data that I can copy and paste into an Enter Data query. So, how is your source data ingested into the data model? Is it a single table like:

Date,Product,Sales

1/1/2017,Beef,100

2/1/2017,Beef,1000

1/1/2017,Pork,200

2/1/2017,Pork,300

Like that or more complicated, multiple tables? If I can get a good data model going I can generally come up with a good answer. Otherwise, it's just a guess and the likelihood of getting a useful answer is significantly diminished.

[PaulMarston]

Hi Greg, yes the data is flat. This is a small sample

Date Species Value 1/9/2017 Beef 5,013 1/19/2017 Pork 4,208 2/1/2017 Beef 6,789 3/13/2017 Chicken 4,516 1/18/2017 Chicken 2,696 1/3/2017 Beef 3,865 2/5/2017 Pork 792 2/17/2017 Pork 689 2/10/2017 Chicken 852 3/23/2017 Pork 3,600

[Greg_Deckler]

@PaulMarstonOK, one last question, I hope, what is your [Sales This Year] calculation, the formula? Oh, and I realize this is a sample but in your actual source data, are your dates contiguous?

[Greg_Deckler]

@PaulMarston- OK, instead of waiting for a reply here is what I did. Hopefully this is applicable.

First, I started with this query based upon the data that you provided but also added data for 2018:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdAxDgIhEIXhu1BvwhsQeLR6AfvNVmaNZhNNvH8hbsDsQEXxZ2D45tmIzdZBkpnMeV3v5QgQb5bpl+Tfru/PVo6TA/fmrHRjMTHvyVvxrV0ez9u2vn6TQWK7lWN2Mbfsu4s9Y6hvhm6dlF3bJnUp1m1Kwvgcg6u7Ot8N+gjUTfbfU8tA0VDT4GhDbQOFwwEHSoeDDo481Dw4+lD5QAFRAUEJsReCIqImKnH5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Species = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Species", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

Second, I created a calendar table like this:

Calendar = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))

This is so I have contiguous dates, which SAMEPERIODLASTYEAR likes. I then created a relationship between my #Sales table and this Calendar table like this:

Calendar 1->* #Sales

Again, this is for the benefit of SAMEPERIODLASTYEAR for the most part but most time intelligence functions work better with a date dimension table.

Now that I have those, I created the following measures:

Sales This Year = TOTALYTD(SUM('#Sales'[Value]),'Calendar'[Date])

Sales Last Year = CALCULATE([Sales This Year],SAMEPERIODLASTYEAR('Calendar'[Date]))

I also created a Month column in my #Sales table like this:

Month = FORMAT([Date],"mmm")

Now I can put Month, Sales This Year and Sales Last Year into a table visualization and get the expected result. I can also put Species, Sales This Year and Sales Last Year into a table visualization and get the expected result.

In my case, I just added a zero to all of the 2018 entries so that 2018 was 10x of 2017 so that it was easy to check the numbers.

[PaulMarston]

Hi Greg, I’ve completed the above but I can’t see how to join the two tables in this way “Calendar 1->* #Sales”

[Greg_Deckler · Accepted solution]

OK, go to your Relationship view, third icon down on the left. Looks like three tables.

Now, drag Date from your Calendar table to Date in your Sales table:

Double click the line and set the following:

[PaulMarston]

Greg that's perfect thanks for your help

[PaulMarston]

Thanks Greg I will try this

[PaulMarston]

Greg can I attach a file to a post

[PaulMarston]

Hi Greg

I'm using this calculation to get the sales value for last year

Sales Last Year = CALCULATE([Sales This Year],SAMEPERIODLASTYEAR(Sales[Date]))

I can then see

Month    Sales Last Year

Jan                   10,000

Feb11,150

March12,300