cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chreds
Regular Visitor

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-01 0:00	5		5		10
2016-04-01 0:00	5		5		15
2016-05-01 0:00	5		5		20
2016-06-01 0:00	5		5		25
2016-07-01 0:00	5		5		30
2016-08-01 0:00	5		5		35
2016-09-01 0:00	5		5		40
2016-10-01 0:00	5		5		45
2016-11-01 0:00	5		5 		50
2016-12-01 0:00	5		5 		55
2017-01-01 0:00	5		5		5

 

 I've tried the following:

1.

Cumulative = CALCULATE(SUM(Table[Recurring]), DATESYTD(Table[Month]))

And

2.

Cumulative = CALCULATE(SUM(Table[Recurring]), FILTER(ALL(Table[Month]), Table[Month] <= MAX(Table[Month])))

#2, especially I've seen in other examples such as here: http://www.daxpatterns.com/cumulative-total/

 

It's just calculating the SUM over the one row instead of the filtered set.

 

Any ideas?


If I change to FILTER(ALL(Table), ...

That is, to exclude the column, then I get the SUM over all the table. I'm looking for the SUM *up to* that point in the table... a cumulative sum.

 

Thanks! Any help would be greatly appreciated.

 

1 ACCEPTED SOLUTION

@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

 

1.PNG 

Konstantinos Ioannou

View solution in original post

9 REPLIES 9
Howard_Hong
Frequent Visitor

Using the same table, but assuming two additional years of data (5 recurring per month),, how can I get a monthly average for the three years combined?

 

Thanks,

Howard

austinsense
Impactful Individual
Impactful Individual

You've got this almost exactly right but there are a few issues - but I guess that's why you created this post Smiley Happy

 

For Formula #2

Here's the logic - for every row of the table, that row doesn't "know" that the other rows exist.  For example in Feb 2016 (row 2) that row is only aware of data in Feb 2016.  Using the ALL function in your formula overcomes that by telling the formula to consider all of the dates and not just the results from the current row.  Then once you have all the dates you re-establish a window of dates that you want to see.

 

First Error - You're asking in the formula to return all the months ALL(Table[Month]) but it looks like you're using Table[Date] in your report.  If that's the case, change the formula to ALL(Table[Date]) for starters.  But really to @konstantinos point you need to create a separate date table.  Always, always use a separate date table with contiguous dates.

 

Second Error - you haven't included an argument for dealing with the YTD aspect - sounds like you want a) everything before this date b) that's also in this year.

 

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

 

 

For Formula #1

This should work fine once you have the date table setup

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

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

 

 

@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

 

1.PNG 

Konstantinos Ioannou

My requirement is to display the cumulative sum for Actual and Scheduled Quantity from the start of the current month to the current day of the current month. Scheduled Quantity would be for the entire current month.

 

Current Month is Feb so X-axis would have days from 1 to 28. Y-Axis would show both cumulative sum of actual and scheduled quantity.

 

The actual and scheduled quantity is from the same table which also contains date key. The date key is common between this table and the date table.

 

Cumulative Scheduled Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Scheduled_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))

 

Cumulative Actual Quantity = CALCULATE(SUM(F_PROJECT_PROGRESS_WORKMEN[Actual_QTY]),FILTER(ALL(D_DATE[Date_Key]),D_DATE[Date_Key]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1) && D_DATE[Date_Key]<=[Today's Date]))

 

These are the measures I created as I can't create Columns in Direct query mode unless I am misinformed on something here.

 

Please help me with the same as I need to turn this around by today. Thanks in advance.

Cghiurca
Frequent Visitor

NIV YTD = CALCULATE(SUM(RO_01ViewSalesSplit[NIV]),FILTER(RO_01ViewSalesSplit,RO_01ViewSalesSplit[Luna]<=8)

 

However the displayed values are still at month level and I do not get a Runing Total

Luna NIV NIV YTD
1 15323061.53        15323061.54
2 17546153.57         17546153.57
3 21148304.41         21148304.41
4 16476112.20         16476112.20
5 17277334.76          17277334.76
6 17576768.19          17576768.19
7 19528658.91           19528658.91
8 20691773.40           20691773.40

konstantinos
Memorable Member
Memorable Member

It is not clear if you try to create a measure or a calculated column..

 

In order to use time intelligent functions  like DATEYTD()  you need to use a date table ( unigue dates ) and create a relationship with the Table[Month]..and use fileds from your date table in the graph/table.

 

The same is for the pattern..you need to use a different date table..

 

 

 

 

 

Konstantinos Ioannou
GilesWalker
Skilled Sharer
Skilled Sharer

@chreds

 

I needed to create a running total for one of my reports and used the below measure:

 

Running total = CALCULATE(SUM(Table1[Revenue]),FILTER(ALL(Table1[Week_number]),Table1[Week_number]<=MAX(Table1[Wekk_number])))

 

This one worked for, however not sure if it will recalculate on the new year, but perhaps this will help.

 

Thanks,

 

Giles

 

 

@GilesWalker No it won't but you can add VALUES(Dates[Year]) as filter in CALCULATE so it will pass the year as filter..

Konstantinos Ioannou

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors