Forum Discussion
Cumulative total by date
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)
)
)
4 Replies
- parry2k
Super User
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.⚡
