Forum Discussion
Solved
Getting YTD var for Waterfall chart
Hi,
I have a dataset (excel attached) where YTD values are missing for a calculation to get a waterfall chart.
I do have MTD values though. I created a measure to calculate the YTD however for a waterfall I need a diff in this YTD value (current to prev month and 12 month must be same as the YTD value for each year). I was unable to achieve it. Please assist.
| This I have | This I have | This I have | This I have | This I have | This I have | this I want | this I want | comment |
| Fiscal Month | Fiscal Year | Link_key | HU Revenue | Total Revenue | Agregation Type | HU YTD | HU YTD VAR | |
| 1 | 2021 | 2021-1-All Branches - YTD | 61213259.28 | All Branches - YTD | 0.338682263 | 0.338682263 | ||
| 2 | 2021 | 2021-2-All Branches - YTD | 126097574 | All Branches - YTD | 0.357244748 | 0.018562486 | ||
| 3 | 2021 | 2021-3-All Branches - YTD | 211024649.7 | All Branches - YTD | 0.386719451 | 0.029474703 | ||
| 4 | 2021 | 2021-4-All Branches - YTD | 279431733.5 | All Branches - YTD | 0.388797488 | 0.002078037 | ||
| 5 | 2021 | 2021-5-All Branches - YTD | 355584617.7 | All Branches - YTD | 0.393658766 | 0.004861277 | ||
| 6 | 2021 | 2021-6-All Branches - YTD | 455775053.3 | All Branches - YTD | 0.408074845 | 0.014416079 | ||
| 7 | 2021 | 2021-7-All Branches - YTD | 541508415.8 | All Branches - YTD | 0.418243489 | 0.010168644 | ||
| 8 | 2021 | 2021-8-All Branches - YTD | 634937824.7 | All Branches - YTD | 0.42984055 | 0.011597061 | ||
| 9 | 2021 | 2021-9-All Branches - YTD | 754934096.9 | All Branches - YTD | 0.448067701 | 0.01822715 | ||
| 10 | 2021 | 2021-10-All Branches - YTD | 858238730.6 | All Branches - YTD | 0.456873208 | 0.008805507 | ||
| 11 | 2021 | 2021-11-All Branches - YTD | 961141449 | All Branches - YTD | 0.463794403 | 0.006921195 | ||
| 12 | 2021 | 2021-12-All Branches - YTD | 1068179302 | All Branches - YTD | 0.472930606 | 0.472930606 | <<it has to be the same on 12 month of the year>> | |
| 1 | 2022 | 2022-1-All Branches - YTD | 78066164.01 | All Branches - YTD | 0.501012724 | 0.028082117 | ||
| 2 | 2022 | 2022-2-All Branches - YTD | 160732719.2 | All Branches - YTD | 0.508988775 | 0.007976051 | ||
| 3 | 2022 | 2022-3-All Branches - YTD | 259696685.2 | All Branches - YTD | 0.529915221 | 0.020926446 | ||
| 4 | 2022 | 2022-4-All Branches - YTD | 341144791 | All Branches - YTD | 0.531788025 | 0.001872804 | ||
| 5 | 2022 | 2022-5-All Branches - YTD | 427414083.6 | All Branches - YTD | 0.534766114 | 0.002978088 | ||
| 6 | 2022 | 2022-6-All Branches - YTD | 534151680.5 | All Branches - YTD | 0.540633926 | 0.005867812 | ||
| 7 | 2022 | 2022-7-All Branches - YTD | 616823729.4 | All Branches - YTD | 0.541061491 | 0.000427565 | ||
| 8 | 2022 | 2022-8-All Branches - YTD | 704854852.6 | All Branches - YTD | 0.540880146 | -0.00018135 | ||
| 9 | 2022 | 2022-9-All Branches - YTD | 812763352.7 | All Branches - YTD | 0.544923119 | 0.004042973 | ||
| 1 | 2021 | 2021-1-All Branches - MTD | 20731845.16 | 61130996.87 | All Branches - MTD | |||
| 2 | 2021 | 2021-2-All Branches - MTD | 24315850.93 | 64884314.68 | All Branches - MTD | |||
| 3 | 2021 | 2021-3-All Branches - MTD | 36559640.64 | 84927075.72 | All Branches - MTD | |||
| 4 | 2021 | 2021-4-All Branches - MTD | 27035019.32 | 68407083.77 | All Branches - MTD | |||
| 5 | 2021 | 2021-5-All Branches - MTD | 31336645.6 | 76152884.27 | All Branches - MTD | |||
| 6 | 2021 | 2021-6-All Branches - MTD | 46011332.45 | 100190435.5 | All Branches - MTD | |||
| 7 | 2021 | 2021-7-All Branches - MTD | 40492035.14 | 85733362.57 | All Branches - MTD | |||
| 8 | 2021 | 2021-8-All Branches - MTD | 46439654.8 | 93429408.88 | All Branches - MTD | |||
| 9 | 2021 | 2021-9-All Branches - MTD | 65339560.83 | 119996272.2 | All Branches - MTD | |||
| 10 | 2021 | 2021-10-All Branches - MTD | 53844696.86 | 103304633.7 | All Branches - MTD | |||
| 11 | 2021 | 2021-11-All Branches - MTD | 53665742.91 | 102902718.4 | All Branches - MTD | |||
| 12 | 2021 | 2021-12-All Branches - MTD | 59402660.14 | 107037852.6 | All Branches - MTD | |||
| 1 | 2022 | 2022-1-All Branches - MTD | 39112141.45 | 78055907.05 | All Branches - MTD | |||
| 2 | 2022 | 2022-2-All Branches - MTD | 42699008.37 | 82666555.15 | All Branches - MTD | |||
| 3 | 2022 | 2022-3-All Branches - MTD | 55806076.56 | 98963966.05 | All Branches - MTD | |||
| 4 | 2022 | 2022-4-All Branches - MTD | 43799488.37 | 81448105.76 | All Branches - MTD | |||
| 5 | 2022 | 2022-5-All Branches - MTD | 47149853.63 | 86269292.61 | All Branches - MTD | |||
| 6 | 2022 | 2022-6-All Branches - MTD | 60213951.66 | 106737596.9 | All Branches - MTD | |||
| 7 | 2022 | 2022-7-All Branches - MTD | 44959046.78 | 82672048.89 | All Branches - MTD | |||
| 8 | 2022 | 2022-8-All Branches - MTD | 47502428.4 | 88031123.22 | All Branches - MTD | |||
| 9 | 2022 | 2022-9-All Branches - MTD | 61651545.71 | 107908500.1 | All Branches - MTD | |||
| 1 | 2022 | 2022-1-SC Branches - YTD | 1869008.49 | 3890338.76 | SC Branches - YTD | |||
| 2 | 2022 | 2022-2-SC Branches - YTD | 1642934.58 | 7439693.15 | SC Branches - YTD | |||
| 3 | 2022 | 2022-3-SC Branches - YTD | 2011660 | 11719352.1 | SC Branches - YTD | |||
| 4 | 2022 | 2022-4-SC Branches - YTD | 2424270.4 | 16830852.15 | SC Branches - YTD | |||
| 5 | 2022 | 2022-5-SC Branches - YTD | 2732441.69 | 22640606.86 | SC Branches - YTD | |||
| 6 | 2022 | 2022-6-SC Branches - YTD | 3802715.35 | 29827201.04 | SC Branches - YTD | |||
| 7 | 2022 | 2022-7-SC Branches - YTD | 3085616.79 | 35749653.66 | SC Branches - YTD | |||
| 8 | 2022 | 2022-8-SC Branches - YTD | 3214685.3 | 41863796.52 | SC Branches - YTD | |||
| 9 | 2022 | 2022-9-SC Branches - YTD | 3893634.37 | 48829098.75 | SC Branches - YTD | |||
| 1 | 2022 | 2022-1-SC Branches - MTD | 3889227.61 | SC Branches - MTD | ||||
| 2 | 2022 | 2022-2-SC Branches - MTD | 3549354.39 | SC Branches - MTD | ||||
| 3 | 2022 | 2022-3-SC Branches - MTD | 4279658.95 | SC Branches - MTD | ||||
| 4 | 2022 | 2022-4-SC Branches - MTD | 5111500.05 | SC Branches - MTD | ||||
| 5 | 2022 | 2022-5-SC Branches - MTD | 5809754.71 | SC Branches - MTD | ||||
| 6 | 2022 | 2022-6-SC Branches - MTD | 7186594.18 | SC Branches - MTD | ||||
| 7 | 2022 | 2022-7-SC Branches - MTD | 5922452.62 | SC Branches - MTD | ||||
| 8 | 2022 | 2022-8-SC Branches - MTD | 6114142.86 | SC Branches - MTD | ||||
| 9 | 2022 | 2022-9-SC Branches - MTD | 6965302.23 | SC Branches - MTD |
HI Sultanista,
I'd like to suggest you take a look at the following link ‘year to date’ part if suitable for your requirement:
Before You Post, Read This: year-to-date, previous year-to-date
Regards,
Xiaoxin Sheng
1 Reply
HI Sultanista,
I'd like to suggest you take a look at the following link ‘year to date’ part if suitable for your requirement:
Before You Post, Read This: year-to-date, previous year-to-date
Regards,
Xiaoxin Sheng