Forum Discussion

chreds's avatar
chreds
Regular Visitor
10 years ago
Solved

DAX - Running Total YTD

I'm trying to calculate a running total YTD and am having trouble getting it to work. Here's my table:   Month Recurring Cumulative Expected 2016-01-01 0:00 0 0 0 2016-02-01 0:00 5 5 5 2016-03...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    chreds The relationship is needed for measure and time intelligence. 

     

    Yes you can create as a calculated column. You calculate the sum of all the previous rows/dates

     

     

    Cumulative = 
VAR RowDate = Table1[Date]
RETURN
    CALCULATE (
        SUM ( Table1[Recurring] );
        FILTER (
            Table1;
            Table1[Date] <= RowDate
                && YEAR ( Table1[Date] ) = YEAR ( RowDate )
        )
    )

    You can use or delete 

     && YEAR ( Table1[Date] ) = YEAR ( RowDate )

    if you need YTD or Cumulative Life to date

     

     

chreds's avatar
chreds
Regular Visitor
10 years ago

Thanks austinsense, konstantinos . That's great info. I've setup a DatesTable now.

 

Is there a way to do this as a Calculated Column instead of a Measure? I have this working as a measure now but I'd like to be able to work with the information as a column instead of this just being available at the report level.

 

1.

 

CALCULATE(SUM(Table[Recurring]), DATESYTD(DatesTable[Date]))

 

 

and 

 

2. 

 

Cumulative = CALCULATE(SUM(Table[Recurring]), 
FILTER(ALL(DateTable[Date]),
  DateTable[Date] <= MAX(DateTable[Date]) &&
  DateTable[Year] = YEAR(MAX(DateTable[Date]))
))

 

 

Both are just calculating the same value as in the Recurring column and not the cumulative total for that year.

 

What should the relationship be between the two tables?

 

My tables look like this now:

 

Table:

Date		Recurring	Cumulative (Calculated Column)
2016-01-01 0:00	0		0		
2016-02-01 0:00	5		5		
2016-03-01 0:00	5		5		
2016-04-01 0:00	5		5		
2016-05-01 0:00	5		5		
2016-06-01 0:00	5		5		
2016-07-01 0:00	5		5		
2016-08-01 0:00	5		5		
2016-09-01 0:00	5		5		
2016-10-01 0:00	5		5		
2016-11-01 0:00	5		5 	
2016-12-01 0:00	5		5 		
2017-01-01 0:00	5		5

 

DateTable:

Date		Year
2016-01-01 0:00	2016
2016-02-01 0:00	2016
2016-03-01 0:00	2016
2016-04-01 0:00	2016
2016-05-01 0:00	2016
2016-06-01 0:00	2016
2016-07-01 0:00	2016
2016-08-01 0:00	2016
2016-09-01 0:00	2016
2016-10-01 0:00	2016
2016-11-01 0:00 2016
2016-12-01 0:00	2016	
2017-01-01 0:00	2017

 

 

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

chreds The relationship is needed for measure and time intelligence. 

 

Yes you can create as a calculated column. You calculate the sum of all the previous rows/dates

 

 

Cumulative = 
VAR RowDate = Table1[Date]
RETURN
    CALCULATE (
        SUM ( Table1[Recurring] );
        FILTER (
            Table1;
            Table1[Date] <= RowDate
                && YEAR ( Table1[Date] ) = YEAR ( RowDate )
        )
    )

You can use or delete 

 && YEAR ( Table1[Date] ) = YEAR ( RowDate )

if you need YTD or Cumulative Life to date