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
JT_MSUK's avatar
JT_MSUK
Icon for Advocate I rankAdvocate I
2 years ago

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