Calculating the cumulative sum using a disconnected table

[JT_MSUK]

I had an issue yesterday with displaying the last 5 years on my graph dependent on year selected, which I solved using a disconnected table - it allows me to use my main CalendarTable for the Year and Month slicers.

The issue I now have is I cannot get the YTD values to display on my line graph, I can only seem to pull in the data by Month only (current measures below). The Disconnected Calendar Date heirachy is connected to the MemberData Table date as an inactive relationship:

Members = SUM(MemberData[Members])

Last 5 Years YTD = Calculate([Members],ALL(CalendarTable[Date].[Year]), USERELATIONSHIP(MemberData[Date],'CalendarTable DC'[Date]),DATESYTD(DATEADD('CalendarTable DC'[Date].[Date],0,YEAR)))

I'm not quite sure how to do the initial calculations bit to get the YTD, any advice appreciated.

[ChiragGarg2512]

 , mark the table with date as date column instead of using .[year] . 

For more reference:

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Running Total/ Cumulative: 
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

@JT_MSUK

[JT_MSUK]

The disconnected table DATE column is already a date column with hierachy of month, day and year.

Do you mean remove Year from ALL(CalendarTable[Date].[Year])? If I remove Year it only shows the total for the month selected, the previous 4 years then disappear.

To display the last 5 years I have this in the filter as 1:

Last 5 Years =

VAR YearSelection = SELECTEDVALUE(CalendarTable[Date])

Return

IF(AND(MAX('CalendarTable DC'[Date].[Year])<=YearSelection,MAX('CalendarTable DC'[Date].[Year])>=YearSelection-4),1,0)

[ChiragGarg2512]

@JT_MSUK , Don't mean the data type of the column but mark the tables as date table.

[JT_MSUK]

Unfortunately that doesn't work and breaks my other measures with the date hierarchy being removed from the disconnected calendar. My working cumulative measure on my active CalendarTable is:

Members YTD = CALCULATE([Members],DATESYTD(DATEADD(CalendarTable[Date].[Date],0,Year)))

If I apply Members YTD to the Last 5 Years YTD it'll show the last 5 years, but the same cumulative value for the current year for all 5 years (i.e if this year is 5,000, it'll show 5,000 for 2019-2023)

Last 5 Years YTD = Calculate([Members YTD,ALL(CalendarTable[Date].[Year]), USERELATIONSHIP(MemberData[Date],'CalendarTable DC'[Date]),DATESYTD(DATEADD('CalendarTable DC'[Date].[Date],0,YEAR)))