Forum Discussion
Report data from same week previous year
Appended dataqueries from several companies to a new query in Power BI.
Every company query has almost the same columns (YearWeek, SalesTotal, SalesA, SalesB, SalesC, ........).
Before appending i added two new colums in the companyqueries (CompanyName, YearWeekCompany).
In the new query (with all data from all companys) three colums were added (Year, Week, Period) - in our case a Period stands for 4 weeks.
With the new Query (GROUP) i want to create a "Line and stacked column chart".
In the stacked columns are the Sales A tot Z per Week (or Period).
In the Line we want to present the SalesTotal of the previous year from the same Week.
What is best way to measure this.
I've found several posts on this topic. But none seems to be adequate.
In this case working with SAMEPERIODLASTYEAR and adding a date-table seems not to be the best way. There is only 1 record for each week (per Year per Company).
Tried something like this, but don't how to filter the right company
LASTYEARsalestotal =
CALCULATE(
SUM ('GROUP'[salestotal]);
FILTER (
ALLSELECTED ( 'GROUP');
VALUE('GROUP'[Year]) = Year ( today())-1
&& VALUE('GROUP'[Week]) = WEEKNUM(TODAY())
))
TIA for replying
16 Replies
Hi Nethunt,
Can you share some sample data masking confidential information. What is the final output you expect.
Cheers
CheenuSing
HI Nethunt,
You can add a condition to check current company:
LASTYEARsalestotal = VAR current_company = LASTNONBLANK ( 'GROUP'[Company], [Company] ) RETURN CALCULATE ( SUM ( 'GROUP'[salestotal] ); FILTER ( ALLSELECTED ( 'GROUP' ); VALUE ( 'GROUP'[Year] ) = YEAR ( TODAY () ) - 1 && VALUE ( 'GROUP'[Week] ) = WEEKNUM ( TODAY () ) && 'GROUP'[Company] = current_company ) )Regards,
Xiaoxin Sheng
Thanks Xiaoxin Sheng
Seems like a good begin, but not the final result I need.
In each week of the chart, we want to see in the line the weektotals of last year's week.
So in week 201701 we have stacked data from sales A, B and C (together SalesTotal); in the line should be the SalesTotal form last year 201601). Stacked 201702 together with line 201602 etc.
Chart 2016Our last data is by e.g. week 201739 - this measurement gives only the lastyear data for 201639 as a constant and is only visible when in the chart the year 2016 is selected.
For choosing the year and the company (or several company's) i used a slicer.When changing the year or the company('s) the line should change to the data from the previous year (in this example chart - all the weeks from 2015).
Don't know if this can be done with a measure. Adding columns (with data last year for each YearWeekCompany) gives a double as much data in the query and seems not the most logical solution.
Hi,
Can you share a sample dataset and show the expected result.
