Forum Discussion

JT_MSUK's avatar
JT_MSUK
Icon for Advocate I rankAdvocate I
2 years ago

Calculating the cumulative sum using a disconnected table

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 an...
Need Help
ChiragGarg2512's avatar
ChiragGarg2512
Icon for Solution Sage rankSolution Sage
2 years ago

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

 

 

JT_MSUK's avatar
JT_MSUK
Icon for Advocate I rankAdvocate I
2 years ago

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)))