Cumulative sum per Year - Countdown

[Anonymous]

Hi everyone, 

I'm looking for a solution to create a calculation to sum the cumulative results Per Year for a countdown column.

Hereby an overview of (fake) numbers per Year by the Countdown column. The cumulative calculation needs to start by the highest number and needs to sum all the numbers till the lowest number in the column (WeeksUntil). As the column is a countdown.

[parry2k]

@Anonymous Please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[Anonymous]

Hi @parry2k ,

Hereby a link to a sample table stored in Google Drive.

I've added the actual table and the requested cumulative sum per year.

Would be great if you're able to help me!

Thanks

[parry2k]

@Anonymous solution attached, I have to unpivot your data it to work. Look at power query steps

[Anonymous]

Thanks @parry2k, but doesn't seems like it's fully working, unfortunately. 

When I use your calculated measure in my dashboard, with actual source data, seems like there are still a few differences in the cumulative column...

[parry2k]

@Anonymousyou have to be very specific what is not working otherwise I have no way to know this.

[Anonymous]

@parry2k , there's another field which need to taken in account when you filter on it. There are 7 types of qty, we would like to filter on the results per type as well. For now, the field 'Type' isn't taken  into account. So, when there's a week with no qty for a specific type, there's a zero or it's not listed in the table now, so the qty goes down or is being calculated wrong.

Here's the link to the file with the additional field added. 

[parry2k]

@Anonymous another field surely plays a role on dax, how you are planning to visualize

[Anonymous]

@parry2k 

As you can see in the link it's like a subcategory field. I would prefer to overview the data by total, but would like to filter on a subcategory level as well. But when I filter or select a subcategory, the data is incorrect now. 

Do you've a suggestion?