Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing YTD with last year

Hi!

 

I created a measure for the sum of revenue which is visually compared with the sum of revenue of last year using a KPI card in a dashboard using the following measure:

 

 

Revenue LY = 
CALCULATE(
    [Revenue],
    FILTER(
        ALL(Date[Year]),
        Date[Year] = MAX(Date[Year]) - 1)
)

 

 

But when I, for example, select 2021 as a year, it compares the revenue between January 1st and today with the total revenue of 2020 and not just the same period for that year. Is there a way to ensure when the year is not fulfilled, it compares it with the same period for the year before and otherwise it uses the full years?

 

Thanks in advance!

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Revenue LY fix =
    VAR lastmonthnumber =
    MONTH ( MAX ( Sales[Date] ) )
    RETURN
    CALCULATE (
    [Rev Total],
    FILTER (
    ALL ( 'Date' ),
    'Date'[Year]
    = MAX ( 'Date'[Year] ) - 1
    && 'Date'[Month] <= lastmonthnumber
    )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try measures like

     

    YTD QTY forced=
    var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
    var _max = format(_max,"MMDD")
    return
    calculate([Revenue],DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))


    YTD QTY forced=
    var _max = today()
    return
    if(max('Date'[Date])<=_max, calculate([Revenue],DATESYTD('Date'[Date])), blank())
    //or
    //calculate([Revenue],DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
    //calculate(TOTALYTD([Revenue],'Date'[Date]),filter('Date','Date'[Date]<=_max))

     

    LYTD QTY forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    if(max('Date'[Date])<=_max, CALCULATE([Revenue],DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //CALCULATE([Revenue],DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD([Revenue],dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

     

     

    also refer: All About Time Intelligence around Today: https://youtu.be/gcLhhxhXKEI 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Revenue LY fix =
    VAR lastmonthnumber =
    MONTH ( MAX ( Sales[Date] ) )
    RETURN
    CALCULATE (
    [Rev Total],
    FILTER (
    ALL ( 'Date' ),
    'Date'[Year]
    = MAX ( 'Date'[Year] ) - 1
    && 'Date'[Month] <= lastmonthnumber
    )
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM