CY, PY, PTYD

[egsiegel]

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 taken (measure using sameperiodlastyear)
2/1/2021	1000	900
2/2/2021	1500	1100
2/3/2021	1250	1200
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.

[egsiegel · Accepted solution]

I got it!!!!

PYTD Calls = CALCULATE ( [PY Calls], FILTER ( Dates, [date] <TODAY() ) )

seems to work

Thanks for your help

[amitchandak]

@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]

@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.