Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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 2016Chart 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.


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

Thanks for replying. 

 

For the expected result see chart in reply from today.

Simplified the GROUP query is as follows

 

PBI - dataset.png

Hi,

 

Share the data in an Excel or .pbix file.


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

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/

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

 

testdata

 

Hi,

 

This problem will become fairly simple to solve if we have a Date column in your base data table.  Can we have that?


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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks Xiaoxin Sheng, again a little step closer. 

 

PBI - chart 2.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
CheenuSing
Community Champion
Community Champion

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.