Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

datediff (and future dates) and YTD

Hello, 

 

I am using a sparkline visual.   I have a date table.   I am using a simple datediff calculated column formula. 

 

I have looked at various different "hide future dates" solutions and I can't seem to get them to work.  

 

My current 2020 YTD data goes to July 6 2020.      As you can see below, the 2019 data is going until Dec 31 2019.   

 

What I need....is to constrain the 2019 data so that it matches the same YTD period as 2020.     In other words....both 2019 and 2020 data would stop at whatever the current YTD time frame is, so that I can compare the two.  

 

Thanks in advance for any assistance. 

 

Order vs Ship =
DATEDIFF('Orders 2'[Order Date], 'Orders 2'[Date Shipped],DAY)
 
 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , see if these examples can help

    YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[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
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)
//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)

     

    For date diff as measure refer

    https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451