cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
egsiegel
Frequent Visitor

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.
 
1 ACCEPTED SOLUTION
egsiegel
Frequent Visitor

I got it!!!!

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

View solution in original post

3 REPLIES 3
egsiegel
Frequent Visitor

I got it!!!!

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

View solution in original post

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors