Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 week over week. I'm trying to do this by WW and customer name variance
All the DAX measures I've seen are for yearly, quarterly, and monthly. Is there a way to do this by workweek? I also have dates when the forecast was submitted.
Solved! Go to Solution.
@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.
Hi,
What result are you expecting. Please show your result in a spare column.
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.
@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... 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.
Well, if that represents your source data, you can do this with a calculated column using EARLIER and the technique here:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Now, that's a pretty nasty format for week though, you'll have to parse it with something like VALUE(RIGHT([WW]),2))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |