cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nethunt
Frequent Visitor

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 16
Super User I
Super User I

Re: Report data from same week previous year

Hi @Nethunt,

 

Can you share some sample data masking confidential information.  What is the final output you expect.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Community Support
Community Support

Re: Report data from same week previous year

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Nethunt
Frequent Visitor

Re: Report data from same week previous year

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.

 

PBI - chart.pngChart 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.

Super User IV
Super User IV

Re: Report data from same week previous year

Hi,

 

Can you share a sample dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nethunt
Frequent Visitor

Re: Report data from same week previous year

Thanks for replying. 

 

For the expected result see chart in reply from today.

Simplified the GROUP query is as follows

 

PBI - dataset.png

Super User IV
Super User IV

Re: Report data from same week previous year

Hi,

 

Share the data in an Excel or .pbix file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nethunt
Frequent Visitor

Re: Report data from same week previous year

Super User IV
Super User IV

Re: Report data from same week previous year

Hi,

 

I get a message saying that i do not have permission to download the file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Nethunt
Frequent Visitor

Re: Report data from same week previous year

Sorry - first time i used google-drive this way. 

 

testdata

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors