Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
I got it!!!!
I got it!!!!
@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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |