Forum Discussion

vishal17081990's avatar
vishal17081990
Helper I
8 years ago
Solved

Weekly %Variance in sales

HI All,

 

I am new to Power Bi and trying to get weekly % Variance in sales figures.

tried reading n number of articles on forum and tried, however couldnt resolve, looking forward for your help:

 

so i am having below dataset having with Date Column and Sales figures in staples category for Year 2015, 2016 and 2017.

figures in column Tweek are week numbers Tyear is year and TweekNo is week number.

however point to note is week numbers years are calculated at back end in SQL according to financial year hence when we transform date into week or year figures wont match with those present in these columns.

 

i Have created below matrix by putting TYWN in rows, tbl_Calender.TYN in columns and staples in values,

and now i want to look at % variance of TYWN of year 2016 against 2015 and TYWN of year 2017 against 2016.

 

Looking forward for help on this. :)

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    vishal17081990,

    Change %v to the following.

    %v = IF(ISBLANK(MAX(tbl_SalesDaily[previousweekofyear])),0,[DIFFERENCE]/MAX(tbl_SalesDaily[previousweekofyear]))



    Regards,
    Lydia

  • Anonymous's avatar
    Anonymous
    8 years ago

    vishal17081990,

    I have sent you PBIX file via Private Message.

    Regards,
    Lydia

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    vishal17081990,


    Create the following column in your table.

    previousweekofyear = CALCULATE(SUM(Table[Staples]),FILTER(Table,Table[TYWN]=EARLIER(Table[TYWN])&&Table[tbl_Calendar.TYN]=EARLIER(Table[tbl_Calendar.TYN])-1))


    Then create the following measures in your  table and check if you get expected result.

    DIFFERENCE = SUM(Table[Staples])-MAX(Table[previousweekofyear])
    %v = [DIFFERENCE]/SUM(Table[Staples])

    There is an example for your reference.



    Regards,
    Lydia