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[Impressions]);database[finaldate].[Date])

 

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

 

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

 

Thank you very much for your help

 

 

 

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

     

     

12 Replies

  • BetterCallFrank's avatar
    BetterCallFrank
    Resolver IV
    9 years ago
    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
    • ilana105's avatar
      ilana105
      Helper I
      9 years ago

      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

      • BetterCallFrank's avatar
        BetterCallFrank
        Resolver IV
        9 years ago
        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
  • austinsense's avatar
    austinsense
    Impactful Individual
    9 years ago

    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.

    • ilana105's avatar
      ilana105
      Helper I
      9 years ago

      Hello austinsense

       

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