Cumulative total by date

[Anonymous]

I feel like this should be an easy one.

I have a yield column (unpivoted), adjoined by columns for products and years (ie. each row shows yield for a product in a given future year, but all yield values in a single column). I would like the formula for expected cumulative total yield by year, so that I can show a table with product rows and year columns, with cumulative totals filling the table.

Some products have more extensive data than others, with yield data extending for more years.

I have tried the formula below, but the total seems to stop working at the lowest max year across all products, rather than continuing up to the max year for each product individually.

CumTotal =
CALCULATE(
    SUM('Data'[Value]),
    FILTER(
        ALLSELECTED('Data'[Year]),
        ISONORAFTER('Data'[Year], MAX('Data'[Year]), DESC)
    )
)

Thanks

[Anonymous · Accepted solution]

The problem was with the Value column I was trying to sum, rather than the formula per se. Fixed now. Thanks

[v-yiruan-msft]

Hi @Anonymous ，

Could you please mark your post as Answered since the problem has been fixed? And it will be great if you can share the solution here. It will help the others in the community find this solution easily when they face the same problem with you. Thank you.

Best Regards

[Anonymous]

Measure in the original post works fine (which is the version suggested as a quick measure for rolling total). The issue was with the way the my Value column was filtered in power query, which has nothing to do with the functioning of the measure itself.

[parry2k]

@Anonymous when working with dates and using time intelligence it is a best practice to add a date dimension in your model and use that for time intelligence calculations. You can add date dimension by following my blog post here  Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions and then update your measure to use date from date dimension table and in visualization use Year from the date dimension table. 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡