TOTALYTD Question

[vrajkumar1]

Here is my data

Category	Jan-24	Feb-24	Mar-24	Apr-24	May-24
Comm	61,742,741	68,152,538	68,794,916	66,809,865	42,966,036
Md	27,267,359	28,160,052	28,267,516	27,198,823	17,155,274
Mc	82,872,387	83,093,551	88,866,556	86,224,604	55,641,799
OG	4,167,172	4,287,791	3,898,294	4,125,696	2,509,877
SP	4,043,743	4,346,606	4,268,509	4,200,256	2,578,727

I am trying to calculate the YTD values and expect to see the below.

My formula I used was 

Charges_YTD = TOTALYTD(SUM(Table1[Charge]),Table1[Post Date - Date],

Table1[Post Date - Date] = DATE(2024,5,31))

I even removed the last optional part but I am not getting my expected values

What am I missing here ?  Thank you.

Category	Jan-24	Feb-24	Mar-24	Apr-24	May-24
Comm	61,742,741	129,895,279	198,690,195	265,500,060	308,466,096
Md	27,267,359	55,427,411	83,694,927	110,893,750	128,049,024
Mc	82,872,387	165,965,938	254,832,494	341,057,099	396,698,897
OG	4,167,172	8,454,962	12,353,256	16,478,952	18,988,830
SP	4,043,743	8,390,349	12,658,858	16,859,114	19,437,842

[rajendraongole1 · Accepted solution]

Hi @vrajkumar1 - Please create a seperate Date table, it works. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

Hi @vrajkumar1 - I hope you already have the seperate date table and marked it as date, if not please create it.

create a quick date table using the date in your YTD Measure.

Date = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))

YTD Measure:

Charges_YTD =
TOTALYTD(
SUM('Table1'[Charge]),
'Date'[Date]
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[vrajkumar1]

Aah.  So I have to have the Date column in a separate calendar table and it cannot be part of the other data is that the reason ?  Should the Date be always separate ?

[rajendraongole1 · Accepted solution]

Hi @vrajkumar1 - Please create a seperate Date table, it works. 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[vrajkumar1]

Thank you.  It worked for each day calculation.  Not monthly.

[vrajkumar1]

It looks like works.  But I now have a different issue.

Attaching 2 pictures

Its not visible - but I have joined on Post Date - Date and DateKey

If I track Charges YTD as highlighted here - the numbers seem right as it builds on the previous date.

BUT - I just want to see Months not each day.  Creating a date hierachy for that column is not getting me the right number.

I tried to right click to see if Collapse option was there and nothing there.

What am I missing here ?  Thanks.

[vrajkumar1]

Here is my formula

ChargesYTD = TOTALYTD(SUM(Table_2024[Charge]),Calendar[DateKey])

If I choose MonthName from the Calendar table, I get no data.  No charges pop up.

[Anonymous · Accepted solution]

Hi @vrajkumar1 ,

Please try:

Charges YTD = 
VAR LastMonthAvailable = MAX ( 'Calendar'[Month-Year-Sort] )
VAR LastYearAvailable = MAX ( 'Calendar'[Year] )
VAR Category = MAX( 'Table1'[Category] )
VAR Result =
    CALCULATE (
        [Total Charges],
        REMOVEFILTERS ( 'Calendar' ),
        'Calendar'[Month-Year-Sort] <= LastMonthAvailable,
        'Calendar'[Year] = LastYearAvailable,
        VALUES('Table1'[Category])
    )
RETURN
    Result

or:

Charges YTD 2 = CALCULATE([Total Charges],DATESYTD('Calendar'[Date]),VALUES('Table1'[Category]))

or:

Charges YTD 3 = TOTALYTD([Total Charges],'Calendar'[Date],VALUES(Table1[Category]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[vrajkumar1]

Thank you.  I tried the second option and it worked.