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   Date Calls Taken PY Calls...
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
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.