Need help with YTD Calculated Column per record field

[Sashwato]

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:

[TheoC]

Hi @Sashwato 

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

[selimovd · Accepted solution]

Hey @Sashwato ,

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
    vResult

If 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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Sashwato]

Hey Denis,

This is perfect! I got it to work finally. Thank you so much.

Regards!

[amitchandak]

@Sashwato , 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