Forum Discussion
Week over Week Variance - Waterfall Charts
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 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.
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 |
Greg_Deckler That's a good approach. I started to parse my "WW" column I had before.
Greg I found this https://community.powerbi.com/t5/Desktop/Calcu-ate-Last-month-vs-previous-month-variance-in-Matrix/m-p/305058 which I'm trying to do but at a week level. Since there's no weeks recognized in Power BI (only days, years, months, days) I can't do a simple variance column like the examples I see. I wish Power BI has a Work week function.