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.

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