Forum Discussion

egsiegel's avatar
egsiegel
Frequent Visitor
5 years ago
Solved

CY, PY, PTYD

I have a table that holds data on how many calls where taken in a day.

I also have a date table that goes out to 2025 which allows me to show future dates py sales

 

DateCalls TakenPY Calls taken (measure using sameperiodlastyear)
2/1/20211000900
2/2/202115001100
2/3/202112501200
2/4//2021 1500
2/5/2021 1200
2/6/2021 1600

 

I need to create a Gauge Visual that show CY Calls as the Value,  PY Calls for the entire year as the Maximum Value, and PYTD days as the Target value.

 

If I set the visual filter to Dates.Date = This Year and put the CY field in the Value and the PY field in the Maximum...those two pieces work.

 

I am struggling with getting a PYTD field to display correctly.   I have tried various methods including 

PYTD Calls = CALCULATE([CY Calls],SAMEPERIODLASTYEAR(Dates[Date]),Dates[Date] < TODAY())
 
but I still get the total year.
 
Thanks in advance for your help.
 
Need Help
  • egsiegel's avatar
    egsiegel
    5 years ago

    I got it!!!!

    PYTD Calls = CALCULATE ( [PY Calls], FILTER ( Dates, [date] <TODAY() ) )
    seems to work
     
    Thanks for your help

3 Replies

  • egsiegel's avatar
    egsiegel
    Frequent Visitor
    5 years ago

    I got it!!!!

    PYTD Calls = CALCULATE ( [PY Calls], FILTER ( Dates, [date] <TODAY() ) )
    seems to work
     
    Thanks for your help
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    egsiegel , refer to these example with date table

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    forcing the date

     

    YTD QTY forced=
    var _max = today()
    return
    if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
    //or
    //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('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
    if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //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)


    YTD QTY forced=
    var _max = maxx('order',[Order date])
    return
    calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)


    YTD QTY forced=
    var _max = maxx('order',[Order date])
    return
    if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
    //calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
    //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

     

     

    LYTD QTY forced=
    var _max1 =maxx('order',[Order date])
    var _max = date(year(_max1)-1,month(_max1),day(_max1))
    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)

    • egsiegel's avatar
      egsiegel
      Frequent Visitor
      5 years ago

      amitchandak 

      thank you for your very detailed reply.

      Unfortunately, I get the same result that I was getting before.

      the problem is that my table has rows for the entire year....rows through today have CY and PY numbers.  future dates only have PY numbers.

      when I use the PY field in a visual it includes all future dates PY...giving me the full year amount.  (which I also need)

      I think I need 3 fields...

      CY - (from data source)

      PY - CY sameperiodlastyear

      PYTD - CY sameperiodlastyear if the date is today or earlier, else 0....this is the one I am struggling with.