Forum Discussion

ghouse_peer's avatar
ghouse_peer
Icon for Post Patron rankPost Patron
6 years ago
Solved

Ty vs LY

Hello all,

i have two tables:  'Sales' table containing Total sales TY and Total sales LY

 

and 'Time' containing the below details:

 

i need to compare  this year and last year total sales. Kindly help.. 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    If your sales table already TY and LY. What will role of time tables in that now.

     

    Usually, when we have a date using date and time intelligence we create measure like. You might need TD for both

     

    YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) // You might need TD for both TY and LY


    LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
    Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

     

    YTD QTY forced=
    var _max = today()
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

    LYTD QTY forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

     

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

3 Replies

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

    If your sales table already TY and LY. What will role of time tables in that now.

     

    Usually, when we have a date using date and time intelligence we create measure like. You might need TD for both

     

    YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) // You might need TD for both TY and LY


    LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
    Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

     

    YTD QTY forced=
    var _max = today()
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

    LYTD QTY forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

     

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    • ghouse_peer's avatar
      ghouse_peer
      Icon for Post Patron rankPost Patron
      6 years ago

      Hello amitchandak 

       

      according to u, only with TY and LY which is present in table we can show the comparison of last year and this year total sales? If so can u pls  tell me the steps.