Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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
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?
@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.
Hi Greg, yes the data is flat. This is a small sample
|
|
@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?
@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.
Hi Greg, I’ve completed the above but I can’t see how to join the two tables in this way “Calendar 1->* #Sales”
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:
Greg that's perfect thanks for your help
Thanks Greg I will try this
Greg can I attach a file to a post
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |