Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative turnover same period previous year

Hi all,

 

I've been struggling for a while with a formula for cumulative turnover (or whatever variable) in the same period last year.

 

Please see the example below: 

The yelow line is cumulative formula for this year. It is working great and I can apply it on week, month, year, etc. I use true/false filters on my tables and graphs to define the time range, such as 'IsThisYear', 'IsThisWeek', 'IsLastMonth' etc., etc. In the example I've filtered on IsThisYear=true and IsLastWeek=true, but I would like to be able to use other filters of choice as well.

 

The blue line is the turnover of last year. I'm trying to make this line cumulative as well, but I can't get it working with the sameperiodlastyear formule, nor with the dateadd formule and I really don't get why. 

 

The cumulative formula for this year is working like a charm:

 

Cumulative turnover current year =
CALCULATE(
SUM('salesdata'[turnover]),
FILTER(ALLSELECTED('salesdata'),
'salesdata'[DocumentDate]<=MAX('salesdata'[DocumentDate])),VALUES('salesdata'[ISO sales year]))

 

I tried various things for the formula for turnover last year, but I can't get it working. In the example I'm trying this:

Cumulative turnover Last Year =

CALCULATE(

salesdata[cumulative turnover current year],SAMEPERIODLASTYEAR(salesdata[DocumentDate])

)

 

I also tried to use dateadd and various other things, but no mather what I try the sales data of the day last year are returned, but not summed, but per day.

 

Who can help me out with a solid formula for cumulative last year?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I now completely fixed the issue by simply using the already working formula combined with various columns that filter the date range:

     

    Cumulative turnover =
    CALCULATE(
    SUM('salesdata'[turnover]),
    FILTER(ALLSELECTED('salesdata'),
    'salesdata'[DocumentDate]<=MAX('salesdata'[DocumentDate])),VALUES('salesdata'[ISO sales year]))

     

    Filters:

    Dynamically show this year and the past 2 years:

    This & past 2 ISO-years = IF(VALUE(Timetable[YearISO])>=VALUE(YEAR(TODAY())-2),TRUE(),FALSE()) -> filter TRUE
     
    For week:
    IsThisWeeknumber=IF(VALUE(Timetable[Weeknumber ISO]) = WEEKNUM(TODAY(),21), True, False) -> filter TRUE
     
    For month:
    IsThisMonth = IF(VALUE(Timetable[Monthnumber]) = MONTH(TODAY()), True, False) -> filter TRUE
     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Anonymous when you are working with dates you should use date dimension in your model and then use date from this new dimension in your measure. you can easily add one by following my blog post here.

     

    Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your response, but I do already have a full time table (which I use for the time filters on the formula). Without that my basic formula wouldn't have worked either.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I now completely fixed the issue by simply using the already working formula combined with various columns that filter the date range:

     

    Cumulative turnover =
    CALCULATE(
    SUM('salesdata'[turnover]),
    FILTER(ALLSELECTED('salesdata'),
    'salesdata'[DocumentDate]<=MAX('salesdata'[DocumentDate])),VALUES('salesdata'[ISO sales year]))

     

    Filters:

    Dynamically show this year and the past 2 years:

    This & past 2 ISO-years = IF(VALUE(Timetable[YearISO])>=VALUE(YEAR(TODAY())-2),TRUE(),FALSE()) -> filter TRUE
     
    For week:
    IsThisWeeknumber=IF(VALUE(Timetable[Weeknumber ISO]) = WEEKNUM(TODAY(),21), True, False) -> filter TRUE
     
    For month:
    IsThisMonth = IF(VALUE(Timetable[Monthnumber]) = MONTH(TODAY()), True, False) -> filter TRUE
     
  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    Try if this measure works

    Cumulative turnover LY =
CALCULATE (
    SUM ( 'salesdata'[turnover] ),
    FILTER (
        ALLSELECTED ( 'salesdata' ),
        'salesdata'[DocumentDate] <= EDATE ( MAX ( 'salesdata'[DocumentDate] ), -12 )
    )
)

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks v-jingzhang. The line isn't appearing though if I put the filter 'is this year' on true.

      For now I fixed it a bit differently. In this case I have used the formula for a cumulative week overview with current year and last year. I used the working formula as my primary values for the graph. Then I filtered on true for 'IsThisWeek' = IF(VALUE(Timetable[Weeknumber ISO]) = WEEKNUM(TODAY(),21), True, False).

      I used day name on the x-axis and Year ISO for the legenda.

       

      The only disadvantage of this method is that it show all the years while I only want this year and previous year (or maybe in some cases also 2 years back). The values are correct, but it requires me to adjust the filter 'year' every year to show only current year and last year. Not such a big issue, but would have been nice if I can automize it fully.