Need help in creating YTD Measure

[Anonymous]

Hello Team, 

Need help in calculating the measure where I want the volume from 1st-Jan-2020 till today(08-July-2020), and so for the previous years i.e. 1st-Jan-2019 till 08-July-2019, 1st-Jan-2018 till 08-July-2018, 1st-Jan-2017 till 08-July-2017 etc. When it will be tomorrow, then I want volume from 1st-Jan-2020 till 09-July-2020, 1st-Jan-2019 till 09-July-2019, 1st-Jan-2018 till 09-July-2018, 1st-Jan-2017 till 09-July-2017 etc. 

Last_Year_Volume =

var maxdate_ = CALCULATE(TODAY(), ALL(ALL_DATE_DIM[Month-Year]))

var preyear_ = DATE(CALCULATE((YEAR([maxdate_])-1)), MONTH([maxdate_]), DAY([maxdate_]))

RETURN

CALCULATE(SUM(QUALITY_RESULT_FACT[VOLUME_KG]), ALL(ALL_DATE_DIM), ALL_DATE_DIM[Month-Year]-preyear_)

Note: ALL_DATE_DIM is related with QUALITY_RESULT_FACT and it's correct. No problem with joining as other results are coming proper. 

But it's not working. Please help. 

[v-jayw-msft]

Hi @Anonymous ,

Generally, you want to calculate value from start of this month to today the value of the same period last year?

Create measure for each year like this?

2020 = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&'Table'[date]<=TODAY()))

2019 = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date]>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)&&'Table'[date]<=EDATE(TODAY(),-12)))

2018 = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[date]>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),1)&&'Table'[date]<=EDATE(TODAY(),-24)))

Best Regards,

Jay

[Anonymous]

Hello @v-jayw-msft ,

Thanks for your response. 

In  this case, we need to create another measure in 2021, and again 2022 etc. This again comes with a dependancy. I don't want any dependancy. 

Also, I want to calculate the value from starting of the year till today and the value of the same period of all the years. 

Thanks. 

Regards,

Anirban

[amitchandak]

@Anonymous , With time intelligence and date table

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)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hello @amitchandak , 

Thanks for your reply. 

In your measures, it will canculate only current year and previous year. Need to calculate all the years and when the new data will come, it should calculate for the new data as well. 

I want to calculate the value from starting of the year till today and the value of the same period of all the years. 

Thank you, 

Regards,

Anirban

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?