Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grantlee
New Member

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.

 

 

 

sample.PNG

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

What result are you expecting.  Please show your result in a spare column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.  

 

sample.PNG

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

Greg_Deckler
Super User
Super User

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))


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.