Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
Our 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.
Thanks for replying.
For the expected result see chart in reply from today.
Simplified the GROUP query is as follows
Hi,
Share the data in an Excel or .pbix file.
Hi,
I get a message saying that i do not have permission to download the file.
Hi,
This problem will become fairly simple to solve if we have a Date column in your base data table. Can we have that?
Hello,
there is no date in the basequery per company.
Only the first key (YearWeek e.g. 201701) is date related, which we translated to year, week and period.
Please read my posts.
We just want to add a measured field - e.g. LASTYEARSalesTotal. Like a lookup or index function - (year -1) / same week / same company
By example:
YearWeek SalesTotal 201702 10000 201602 9000
In this case in the column-line chart - the column gives in week 2 from 2017 a SalesTotal from 10.000 and the line should give 9.000 (TotalSales from 201602).
Clearer than this i can't put it.
Hi @Nethunt,
I think you need to create a table as the source of slicer, then use the slicer value as the condition to use in formula: (the value 'year' are from the slicer)
LAST YEAR sales total = VAR current_company = LASTNONBLANK ( 'GROUP'[Company], [Company] ) RETURN CALCULATE ( SUM ( 'GROUP'[salestotal] ), FILTER ( ALLSELECTED ( 'GROUP' ), 'GROUP'[Year] = SELECTEDVALUE('Table'[Year]) &&'GROUP'[Week]= MAX('GROUP'[Week]) && 'GROUP'[Company] = current_company ) )
Regards,
Xiaoxin Sheng
Thanks Xiaoxin Sheng, again a little step closer.
As mentioned before, when these were salesdata from the year 2017, the line should give the totals of 2016. Now column and line are from the same year.
Greetings
Hi @Nethunt,
I think you can add '-1' to filter on pervious year.
LAST YEAR sales total = VAR current_company = LASTNONBLANK ( 'GROUP'[Company], [Company] ) RETURN CALCULATE ( SUM ( 'GROUP'[salestotal] ), FILTER ( ALL( 'GROUP' ), 'GROUP'[Year] = SELECTEDVALUE('Table'[Year])-1 &&'GROUP'[Week]= MAX('GROUP'[Week]) && 'GROUP'[Company] = current_company ) )
Regards,
Xiaoxin Sheng
Hello @v-shex-msft,
Thanks for your reaction.
I tried that already. After that the line disappears.
So probably something with text / value indentification ?
In both tables 'GROUP' and 'YEAR', the datatype for [YEAR] is set on whole number.
I even tried to add a column [YEAR-1] in the 'YEAR' table and picked thisone as a selectedvalue; in that case the line isn't also visable.
Hi @Nethunt,
If you can please share the pbix file for further testing.
Regards,
Xiaoxin Sheng
Hi @Nethunt,
Can you share some sample data masking confidential information. What is the final output you expect.
Cheers
CheenuSing
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |