YTD measure

[Anonymous]

Hello, 

Hope you're doing well.

Is there anay way to filter data using time intelligence ? I want to calculate the sum of the sales of last year starting from the 1st january 2019 until the a complete month. Lets pretend we are still in august, I want to cumulate the sales and compare oranges to oranges (Sales from 1st Jan 2019 to 31st Aug 2019 vs Sales from 1st Jan 2020 to 31st Aug 2020) and make it automatic by using a calendar table.

Happy new year.

Regards,

Othman

[Ashish_Mathur]

Hi,

Create a Calendar Table and establish a relationship from the Date column in your base data table to the date column in your Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month Name and Month number.  Sort the Month Name column by Month number.  Create 2 slicers - one for Year and another for Month Name.  In the Year slicer, select 2020 and in the Month Name slicer, select August.  Write these measures

Measure 1 = sum(data[sales])

Measure 2 = calculate([measure 1],datesytd(calendar[date],"31/12"))

Measure 3 = calculate([measure 2],sameperiodlastyear(calendar[date]))

Hope this helps.

[amitchandak]

@Anonymous , use this in return, if date table is not in context

return
 CALCULATE(Sales[Sales],DATESYTD(DATEADD('Date'[Date],-1,YEAR)),'Date'[Date]<=_max)

[amitchandak]

@Anonymous , You might have force it by today, max date or selected 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)

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)

in place of today or maxx('order',[Order date])

you can take selected date maxx(allselected('Date'), 'Date'[Date])

[Anonymous]

Hi @amitchandak,

It shows me blank. I've done something wrong ?

[V-lianl-msft]

Hi @Anonymous ,

You could also refer to this blog:

https://www.kasperonbi.com/get-the-ytd-of-the-same-period-last-year/ 

Happy new year.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - So yes you can use things like PARALLELPERIOD or DATEADD. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008