Forum Discussion

ilana105's avatar
ilana105
Helper I
9 years ago
Solved

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[Imp...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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.

     


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


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


    Best Regards,
    Angelia

     

     

ilana105's avatar
ilana105
Helper I
9 years ago

 

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

 

Thank you very much

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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.

 


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


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


Best Regards,
Angelia