I am able to calculate cumulative values weekwise for each country of the specific year but we are not able to get one requirement i.e; if we dont have any data or row in table for specific workweek to some countries, then it need to be considered previous week cumulative value of missing countries which should be considered for next workweeks.
Example:
Table values before calculating cumulative Value(Base Table):
| Value | WW | year | Country |
| 100 | 1 | 2024 | India |
| 23 | 1 | 2024 | Chaina |
| 87 | 1 | 2024 | Malasiya |
| 53 | 1 | 2024 | Canada |
| 5 | 1 | 2024 | Russia |
| 2 | 2 | 2024 | India |
| 3 | 2 | 2024 | Chaina |
| 1 | 2 | 2024 | Malasiya |
| 2 | 2 | 2024 | Canada |
| 1 | 2 | 2024 | Russia |
| 0 | 3 | 2024 | India |
| 7 | 3 | 2024 | Malasiya |
| 3 | 4 | 2024 | India |
| 24 | 5 | 2024 | India |
| 6 | 5 | 2024 | Chaina |
| 20 | 5 | 2024 | Malasiya |
| 11 | 5 | 2024 | Canada |
| 2 | 5 | 2024 | Russia |
| 0 | 6 | 2024 | Chaina |
| 1 | 6 | 2024 | Malasiya |
| 1 | 7 | 2024 | Chaina |
| 1 | 7 | 2024 | Russia |
| 1 | 8 | 2024 | Malasiya |
| 0 | 9 | 2024 | India |
| 13 | 9 | 2024 | Chaina |
| 1 | 9 | 2024 | Canada |
| 0 | 10 | 2024 | India |
| 12 | 10 | 2024 | Chaina |
| 0 | 10 | 2024 | Malasiya |
| 2 | 10 | 2024 | Russia |
| 0 | 11 | 2024 | Chaina |
Expected Output:
| Value | Cumulative Value | WW | year | Country |
| 100 | 100 | 1 | 2024 | India |
| 23 | 23 | 1 | 2024 | Chaina |
| 87 | 87 | 1 | 2024 | Malasiya |
| 53 | 53 | 1 | 2024 | Canada |
| 5 | 5 | 1 | 2024 | Russia |
| 2 | 102 | 2 | 2024 | India |
| 3 | 26 | 2 | 2024 | Chaina |
| 1 | 88 | 2 | 2024 | Malasiya |
| 2 | 55 | 2 | 2024 | Canada |
| 1 | 6 | 2 | 2024 | Russia |
| 0 | 102 | 3 | 2024 | India |
| | 55 | 3 | 2024 | Canada |
| | 6 | 3 | 2024 | Russia |
| | 26 | 3 | 2024 | Chaina |
| 7 | 95 | 3 | 2024 | Malasiya |
| | 55 | 4 | 2024 | Canada |
| | 6 | 4 | 2024 | Russia |
| | 26 | 4 | 2024 | Chaina |
| | 95 | 4 | 2024 | Malasiya |
| 3 | 105 | 4 | 2024 | India |
| 24 | 129 | 5 | 2024 | India |
| 6 | 32 | 5 | 2024 | Chaina |
| 20 | 115 | 5 | 2024 | Malasiya |
| 11 | 66 | 5 | 2024 | Canada |
| 2 | 8 | 5 | 2024 | Russia |
| 0 | 32 | 6 | 2024 | Chaina |
| | 66 | 6 | 2024 | Canada |
| | 129 | 6 | 2024 | India |
| | 8 | 6 | 2024 | Russia |
| 1 | 116 | 6 | 2024 | Malasiya |
| | 66 | 7 | 2024 | Canada |
| | 116 | 7 | 2024 | Malasiya |
| | 129 | 7 | 2024 | India |
| 1 | 33 | 7 | 2024 | Chaina |
| 1 | 9 | 7 | 2024 | Russia |
| | 129 | 8 | 2024 | India |
| | 66 | 8 | 2024 | Canada |
| | 33 | 8 | 2024 | Chaina |
| | 9 | 8 | 2024 | Russia |
| 1 | 117 | 8 | 2024 | Malasiya |
| | 9 | 9 | 2024 | Russia |
| | 117 | 9 | 2024 | Malasiya |
| 0 | 129 | 9 | 2024 | India |
| 13 | 46 | 9 | 2024 | Chaina |
| 1 | 67 | 9 | 2024 | Canada |
| 0 | 129 | 10 | 2024 | India |
| 12 | 58 | 10 | 2024 | Chaina |
| | 67 | 10 | 2024 | Canada |
| 0 | 117 | 10 | 2024 | Malasiya |
| 2 | 11 | 10 | 2024 | Russia |
| 0 | 58 | 11 | 2024 | Chaina |
| | 11 | 11 | 2024 | Russia |
| | 117 | 11 | 2024 | Malasiya |
| | 129 | 11 | 2024 | India |
| | 67 | 11 | 2024 | Canada |
Output we got with our measure:
Table data after adding Cumulativevalue measure:
In below table Cumulative value is calculated by measure Cumulativevalue =
calculate(sum('Table'[[Value]]]),filter(ALL('Table'),'Table'[year]=values('Table'[year]) && 'Table'[ww]<=max('Table'[ww]) &&'Table'[country] in values('Table'[country])
)
)
In below table if we observe for ww in(3,4,6,7,8,9,10,11) missing data for some countries in this case we need to be considered the cumulative value of previous week for all missing countries in any of the week.
| Value | Cumulative Value | WW | year | Country |
| 100 | 100 | 1 | 2024 | India |
| 23 | 23 | 1 | 2024 | Chaina |
| 87 | 87 | 1 | 2024 | Malasiya |
| 53 | 53 | 1 | 2024 | Canada |
| 5 | 5 | 1 | 2024 | Russia |
| 2 | 102 | 2 | 2024 | India |
| 3 | 26 | 2 | 2024 | Chaina |
| 1 | 88 | 2 | 2024 | Malasiya |
| 2 | 55 | 2 | 2024 | Canada |
| 1 | 6 | 2 | 2024 | Russia |
| 0 | 102 | 3 | 2024 | India |
| 7 | 95 | 3 | 2024 | Malasiya |
| 3 | 105 | 4 | 2024 | India |
| 24 | 129 | 5 | 2024 | India |
| 6 | 32 | 5 | 2024 | Chaina |
| 20 | 115 | 5 | 2024 | Malasiya |
| 11 | 66 | 5 | 2024 | Canada |
| 2 | 8 | 5 | 2024 | Russia |
| 0 | 32 | 6 | 2024 | Chaina |
| 1 | 116 | 6 | 2024 | Malasiya |
| 1 | 33 | 7 | 2024 | Chaina |
| 1 | 9 | 7 | 2024 | Russia |
| 1 | 117 | 8 | 2024 | Malasiya |
| 0 | 129 | 9 | 2024 | India |
| 13 | 46 | 9 | 2024 | Chaina |
| 1 | 67 | 9 | 2024 | Canada |
| 0 | 129 | 10 | 2024 | India |
| 12 | 58 | 10 | 2024 | Chaina |
| 0 | 117 | 10 | 2024 | Malasiya |
| 2 | 11 | 10 | 2024 | Russia |
| 0 | 58 | 11 | 2024 | Chaina |
