Forum Discussion
Cumulative totals (rows)
What is the name of the column you used to give the Year values to the matrix (the field in the Columns field well). I assumed you have a column that has year values in it. Do you have an auto date hierarchy (i.e., you get .[Day], .[Month], etc. after your Date columns?). If not already, please put the same column that is currently on your matrix visual to get Year values inside the VALUES( ) in that measure.
Regards,
Pat
Anonymous this is what I will do, based on my measures, you can apply the same to your measure or the one you are using
DYTD = IF ( ISINSCOPE ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ), [Sales] ) * DIVIDE ( [Sales], [Sales] ) DYTD = SUMX ( VALUES ( 'Calendar'[Year] ), TOTALYTD ( [Sales], 'Calendar'[Date] ) ) * DIVIDE ( [Sales], [Sales] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.⚡
Microsoft EmployeeThat's odd. I have a sandbox model and it seems to work as intended with similar data and same expressions. Please see pic below. Can you send upload the equivalent pic from your model? Are there other columns used in the matrix? FYI that the [TotalYTD Measure] is the same as your initial measure (where I also saw the total value matching the 2020 value).
Is this the kind of result you are looking for?
Regards,
Pat
Hi mahoneypat Thanks and indeed it is.
One of the problems was that I was using the dates(date) instead of dates(year). I fixed that, and added a MIN statment at the beginning because I was continuing to get the same value repeated all the way down the 2020 column (after July). In other words, starting in August, it was just repeating the July value all the way until December. Adding the MIN statment stopped that, but....
But what appears to be happening, is that July's 2020 number is being added to August's 2019 number, and so on.
It's effectively doing what I described above...it's just showing an empty "cell" instead of the actual value...but it is indeed adding $49,695,444 to each of the months in the 2019 column. You can see here, that 78,355,950 + 49,695,444 = 128,051,394. We do not have any orders at all in August (since we are still in July).