cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilana105
Helper I
Helper I

Compare cumulative data between years

Hello commnuity

 

I want to compare the cumulative of one variable over 2 years. 

 

When I use the TOTALYTD formula I get the data I want:

 

 

cumulativeImpressions = TOTALYTD(sum(database[Impressions]);database[finaldate].[Date])

Capture01.JPG

 

However, now I'm importing the data with Direct Query and I'm using a formula to calculate it, The problem is that when I use the formula the cumulative continues in 2016 with the data in 2015

 

cumulativeImpressions = 
CALCULATE (
    SUM (database[impressions] );
    FILTER(ALL(database);
database[finaldate] <= MAX(database[finaldate]))
)

Capture.JPG

 

I want to obtain with the formula the same result as with TOTALYTD function

 

Thank you very much for your help

 

 

 

1 ACCEPTED SOLUTION

Hi @ilana105,

 

How do you set the Axis level, you have year and month field in your source data, you select the month as axis level, the year as legend level, right? If it is, you’d better add filter in measure to cumulative sum for each year, rather than all data. The TOTALYTD Function evaluates the year-to-date value of the expression in the current context. So it return the total sum for each year.


I try to reproduce your scenario as follows.


Create month and year calculated columns.

 

Year = YEAR(Sales[DATE])
Year = YEAR(Sales[DATE])


Create measure using the formula below. The values function will return a table including one year.

 

cumulative = CALCULATE(SUM(Sales[SALE]),FILTER(ALL(Sales),Sales[DATE]<=MAX(Sales[DATE])),VALUES(Sales[Year]))


Create the line chart, you will get expected result same to using TOTALYTD function like the following screenshot.

 

1.PNG

TotalYTD = TOTALYTD(SUM(Sales[SALE]),Sales[DATE])


2.PNG

If you have any other issue, please feel free to ask.


Best Regards,
Angelia

 

 

View solution in original post

12 REPLIES 12
austinsense
Impactful Individual
Impactful Individual

I'm not sure if this is the best approach but I usually solve this by putting an if in the front ...

 

cumulativeImpressions = 
IF(  SUM (database[impressions] ),

CALCULATE (
    SUM (database[impressions] );
    FILTER(ALL(database);
database[finaldate] <= MAX(database[finaldate]))
),

BLANK()
)

That way we only see the cumulative in periods where there is actual data.  This might not solve your problem exactly but it may help you get to a good answer.

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

Hello @austinsense

 

Thank you very much! It doesn´t help me to answer my problem but indeed will provide better data quality

BetterCallFrank
Resolver IV
Resolver IV

Hi Ilana,
You need to add one more condition to your calculate:

Calculate(
...
, year(db[finaldate]) = max(year(final date))
)

To restart the sum for each year

Hth,
Frank

Hello @BetterCallFrank

 

I have tried to add that new condition but I get the following error:

 

The MAX function only accepts a column reference as an argument.

 

Thank you very much

Hi ilana,
Sorry it's the other way around:

Year(final date) = year(max(final date))

If it's not working please let me know

 

Hello @BetterCallFrank

 

I have tried it but it is not working. Please find attached the formula and the graph

 

cumulativeImpressions = 
CALCULATE (
    SUM (database[impressions] );
    FILTER(ALL(database);
database[finaldate] <= MAX(database[finaldate])
&& year( database[finaldate]) = year(MAX(database[finaldate]))
)
)

Capture.JPG

 

Thank you very much

Hi @ilana105,

 

How do you set the Axis level, you have year and month field in your source data, you select the month as axis level, the year as legend level, right? If it is, you’d better add filter in measure to cumulative sum for each year, rather than all data. The TOTALYTD Function evaluates the year-to-date value of the expression in the current context. So it return the total sum for each year.


I try to reproduce your scenario as follows.


Create month and year calculated columns.

 

Year = YEAR(Sales[DATE])
Year = YEAR(Sales[DATE])


Create measure using the formula below. The values function will return a table including one year.

 

cumulative = CALCULATE(SUM(Sales[SALE]),FILTER(ALL(Sales),Sales[DATE]<=MAX(Sales[DATE])),VALUES(Sales[Year]))


Create the line chart, you will get expected result same to using TOTALYTD function like the following screenshot.

 

1.PNG

TotalYTD = TOTALYTD(SUM(Sales[SALE]),Sales[DATE])


2.PNG

If you have any other issue, please feel free to ask.


Best Regards,
Angelia

 

 

View solution in original post

How would you limit each year to not show data beyond the current date?    In other words, the cumulative year to date value for each of the years, but equivalent to today's current date.    That way you are "hiding" the future dates and only comparing dates that are relevant to the current year to date.  

 

This has been really helpul but how do I make the graph be interactive by another column.

 

For example.  If the sales in the graph are made by 5 sales people how can I show on the YTD trends for one of those sales persons.

 

It doesn't work.  I am assuming it is something to do with the filter function?  

KristinaSp
Frequent Visitor

Hello, Neilr,

Did you manage to solve the problem with interation to another columns? I have the same problem now.

Thanks Angelia. Smiley Happy

Adding the 

VALUES(Sales[Year])

part to the end of my calculate function worked. Awesome. I've been searching and trying for ages! 

This I have been looking for!!! All other answers gave me cumulative amounts year-to-year, not being able to compare year vs year.

Thank you!!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors