Forum Discussion

Nethunt's avatar
Nethunt
Frequent Visitor
8 years ago

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Nethunt's avatar
Nethunt
Frequent Visitor
8 years ago

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

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

  • Nethunt's avatar
    Nethunt
    Frequent Visitor
    8 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Nethunt,

     

    If you can please share the pbix file for further testing.

     

    Regards,
    Xiaoxin Sheng