Forum Discussion
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 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.
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.
7 Replies
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/339586
Now, that's a pretty nasty format for week though, you'll have to parse it with something like VALUE(RIGHT([WW]),2))
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.
