SUMMARIZECOLUMNS + time intelligence = not true?

[Anonymous]

Hi!

I am trying to, every year, dynamically sum the capital cost of the last 6 years in order to use it for calculating depreciation, i.e. something like this:

Project name     Year       SumOFCapexLast6yrs     

Project 1            2017      10.000

Project 2            2018      384.959

etc...

I have tried the following query below, but it only sums up the cost for the same year, and I also tried PREVIOUSYEAR just to see if that worked with time intelligence, but the "SumPrevYr"is empty. For info, the DimDate-table is marked as Date Table, and there are relationships between DimDate, Data_fact and Projects

SUMMARIZECOLUMNS (
    Projects[Project name],
    DimDate[Year],
    FILTER ( Data_fact, Data_fact[Categories Level 1] = "Capex" ),
    "SumOFCapexLast6yrs", CALCULATE (
        SUM ( Data_fact[Value (mill, thousands)] ),
        ALL(DimDate[Date]),
        DATESINPERIOD ( DimDate_test[Date], MAX ( DimDate_test[Date] ), -6, YEAR )
    ),
    "SumPrevYr", CALCULATE (
        SUM ( Data_fact[Value (mill, thousands)] ),
        ALL(DimDate_test[Date]),
        PREVIOUSYEAR ( DimDate_test[Date] )
    )
)

Any advice for using time intelligence with summarize or other ways to achieve this?

Much appreciated! 

[Anonymous]

HI @Anonymous,

I'd like to suggest you use date and all functions to manually define the filter range. (time intelligence functions reference its calendar and it will been effect by current summarize table group)

Time Intelligence "The Hard Way" (TITHW)  

Regards,

Xiaoxin Sheng

[amitchandak]

I typically use Totalytd and datesytd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

for rolling

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))

Appreciate your Kudos.