Forum Discussion

grantlee's avatar
grantlee
New Member
8 years ago
Solved

Week over Week Variance - Waterfall Charts

Hi All, I'm trying to create a waterfall chart to spot changes week over week for forecast for particular customers. However I know I need to create a measure to find the variance but I need it for w...
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    grantlee - Yeah, what I would do would be to create a column in your data like this:

     

    Column = VALUE(CONCATENATE(LEFT([WW],4),RIGHT([WW],2))

    This should give you a column like 201750, 201801, etc. as numbers. There is probably a way that you could do this where you would get sequential numbers, which would be even more awesome.

grantlee's avatar
grantlee
New Member
8 years ago

Ashish_Mathur 

 

Hi Ashish,  I'm trying to look what changed each week or between two or more time periods for the demand.  I guess what I'm trying to do is find the Week by Week changes or delta and be able to accummilate the deltas between time periods.  I have more fields such as regions and tech that I would like to add in and be able to create slicers for filter on those.  My example was for simplicity.

 

Greg_Deckler  I agree, I think my WW as text might be what's causing an issue because I can't even use it.  I can't find too many examples but it seems like most folks use WW as numeral (ie. 11 instead of WW11)  I'm trying to dump in the year because I need to capture data in current and parts of last year and to avoid WW #s get mixed up.  2017-WW50 and 2018-WW01 will be in order but if I put 50 and 01 the 01 will be sorted first in any chart I create even though it should be at the end since it's really the newest week.

 

Do you recommend using the Quick Measure Variance?  I tried to use it and seem to got some sort of result but the Variance shows $0.00 for each column.  

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

grantlee - Yeah, what I would do would be to create a column in your data like this:

 

Column = VALUE(CONCATENATE(LEFT([WW],4),RIGHT([WW],2))

This should give you a column like 201750, 201801, etc. as numbers. There is probably a way that you could do this where you would get sequential numbers, which would be even more awesome.

  • grantlee's avatar
    grantlee
    New Member
    8 years ago

    Greg_Deckler  Thanks Greg.  I might have to do that and get rid of the "WW" in my raw data file as well.  I never like to mix text and numbers together when it should be numeric in the first place.

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      BTW, if you can get a Year and WeekNum column through parsing then you can do this kind of tricky Measure to get a sequential number for your years and weeks.

       

      Measure = 
VAR MaxWeeks = SUMMARIZE(ALL('#YearWeeks'),'#YearWeeks'[Year],"MaxWeek",MAX('#YearWeeks'[WeekNum]))
VAR MyYear = MAX('#YearWeeks'[Year])
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('#YearWeeks'[Year],1),ALL('#YearWeeks'))
VAR myNum = IF(MAX('#YearWeeks'[Year])=firstYear,MAX('#YearWeeks'[WeekNum]),MyStart+MAX('#YearWeeks'[WeekNum]))
RETURN myNum

      Here it is as a column:

       

      Column = 
VAR MaxWeeks = SUMMARIZE(ALL('#YearWeeks'),'#YearWeeks'[Year],"MaxWeek",MAX('#YearWeeks'[WeekNum]))
VAR MyYear = [Year]
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('#YearWeeks'[Year],1),ALL('#YearWeeks'))
VAR myNum = IF(MyYear=firstYear,[WeekNum],MyStart+[WeekNum])
RETURN myNum

       

       The data for this looks like:

       

      Table Name: #YearWeeks

       

      Column is the result column from the formula above.

       

      Year        WeekNum      Column

      2016 48 48
      2016 49 49
      2016 50 50
      2016 51 51
      2016 52 52
      2017 1 53
      2017 2 54
      2017 3 55
      2017 4 56
      2017 5 57
      2017 6 58
      2017 7 59
      2017 8 60
      2017 9 61
      2017 10 62
      2017 11 63
      2017 12 64
      2017 13 65
      2017 14 66
      2017 15 67
      2017 16 68
      2017 17 69
      2017 18 70
      2017 19 71
      2017 20 72
      2017 21 73
      2017 22 74
      2017 23 75
      2017 24 76
      2017 25 77
      2017 26 78
      2017 27 79
      2017 28 80
      2017 29 81
      2017 30 82
      2017 31 83
      2017 32 84
      2017 33 85
      2017 34 86
      2017 35 87
      2017 36 88
      2017 37 89
      2017 38 90
      2017 39 91
      2017 40 92
      2017 41 93
      2017 42 94
      2017 43 95
      2017 44 96
      2017 45 97
      2017 46 98
      2017 47 99
      2017 48 100
      2017 49 101
      2017 50 102
      2017 51 103
      2017 52 104
      2018 1 105
      2018 2 106
      2018 3 107
      2018 4 108
      2018 5 109
      2018 6 110