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

 , 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-bd52912a5bd4

 

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