Forum Discussion
Need help with YTD Calculated Column per record field
Hello Team,
I am trying to create a Calculated column that calculates the YTD values per cora_acc_code-accountnumber basis. There are 165 distinct accountnumber records for which I am calculating YTD based on Period date column given in the snapshot below. The period date has values for end of the month and YTD end date is 12/31 in our dataset.
Can anyone help me out with the Calculated column formula per accountnumber record here?
The following are my entries in period date:
Hey Anonymous ,
the following measure should do it. I added some explanations what the mesaure is doing:
YTD Measure = -- save account and date of current row in a variable VAR vAccountRow = myTable[cora_acc_code-accountnumber] VAR vDate = myTable[Period Date] -- calculate the sum and filter table to off rows of -- the current year that are smaller or equal to the row date VAR vResult = CALCULATE ( SUM ( myTable[Sum of Value] ), ALL ( myTable ), myTable[cora_acc_code-accountnumber] = vAccountRow && YEAR ( myTable[Period Date] ) = YEAR ( vDate ) && myTable[Period Date] <= vDate ) RETURN vResultIf you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
4 Replies
- selimovd
Most Valuable Professional
Hey Anonymous ,
the following measure should do it. I added some explanations what the mesaure is doing:
YTD Measure = -- save account and date of current row in a variable VAR vAccountRow = myTable[cora_acc_code-accountnumber] VAR vDate = myTable[Period Date] -- calculate the sum and filter table to off rows of -- the current year that are smaller or equal to the row date VAR vResult = CALCULATE ( SUM ( myTable[Sum of Value] ), ALL ( myTable ), myTable[cora_acc_code-accountnumber] = vAccountRow && YEAR ( myTable[Period Date] ) = YEAR ( vDate ) && myTable[Period Date] <= vDate ) RETURN vResultIf you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic - TheoC
Community Champion
Hi Anonymous
You should be able to use the below and just use a Matrix visual with your Code Column in the rows and the Date as Column headers:
Calculated Column = TOTALYTD ( SUM ( Table[ColumnName] ) , Table[DateColumn] )
Hope this helps 🙂
Theo
- amitchandak
Super User
Anonymous , if you need a new column
new column =
var _year = year([Period date])
var _date =[period date]
return
sumx(filter(Table,[cora_acc_code-accountnumber] =earlier([cora_acc_code-accountnumber]) && year([Period date]) =_year && [period date] =_date),[Sum of Value])If you need a measure use time intelligence
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
