Report data from same week previous year

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?

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 Anonymous,

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