Calculating the cumulative sum using a disconnected table

Question

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 · Answer

&nbsp;, mark the table with date as date column instead of using .[year] .&nbsp;For more reference:Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hwhttps://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4&nbsp;Running Total/ Cumulative:&nbsp;https://www.youtube.com/watch?v=h2wsO332LUo&amp;list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&amp;index=42&nbsp;Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTchttps://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f&nbsp;JT_MSUK

jt_msuk · Answer

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 · Answer

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

jt_msuk · Answer

Unfortunately that doesn't work and breaks my other measures with the date&nbsp;hierarchy being removed from the disconnected calendar. My working cumulative measure on my active CalendarTable is:&nbsp;Members YTD = CALCULATE([Members],DATESYTD(DATEADD(CalendarTable[Date].[Date],0,Year)))&nbsp;If I apply Members YTD to the Last 5 Years YTD&nbsp;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)&nbsp;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)))

Forum Discussion

Calculating the cumulative sum using a disconnected table

4 Replies

Related content

Cumulative Total

PBIX published but disconnected

Your Compute session is disconnected

Calculating cumulative total across categories

Calculating Cumulative Monthly Totals