topic Re: Same period from a Specific Year in DAX Commands and Tips

Same period from a Specific Year

liboyjoseph — Tue, 04 Jan 2022 09:26:17 GMT

Hi Guys,

Need your hand on this,

I have a measure for TY, LY, also 2 Years Ago which is 2019 (When current date selection is on 2021)

Y2 = CALCULATE([Total Sales TY],DATEADD('Date'[Date], -2, YEAR)) This was working perfectly till dec-31- 2021

Now when I change current date to 2022, it will pick up a period from Year of 2020 which is two year ago, is there any way to change this measure to pick the date range always from 2019 without creating a new one? The idea is to bring the value from same date range but always the year should be 2019

Need your help on this please.!

Re: Same period from a Specific Year

amitchandak — Tue, 04 Jan 2022 09:31:19 GMT

liboyjoseph , Based on what I got

Try like

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

2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

or try like

LYTD QTY forced=
var _today = maxx(allselected('Order'),'order'[Date]) // or //Today
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)

2nd LYTD QTY forced=
var _today = maxx(allselected('Order'),'order'[Date]) // or //Today
var _max = date(year(_today)-2,month(_today),day(_today))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-2,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-2,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-2,year),'Date'[Date]<=_max)

Re: Same period from a Specific Year

liboyjoseph — Tue, 04 Jan 2022 09:46:59 GMT

Thank you so much amitchandak , Let me try this and will get back to you

Re: Same period from a Specific Year

liboyjoseph — Tue, 04 Jan 2022 11:43:26 GMT

I've tried all this, but no luck. I think my statment was not clear or you did not get my point. Let me explain

Its similar to sameperiod last year, instad of last year, I need 2019.

Re: Same period from a Specific Year

amitchandak — Tue, 04 Jan 2022 13:53:14 GMT

liboyjoseph , Then you can fix it like

2019 Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'[year]),'Date'[year] =2019))

Re: Same period from a Specific Year

liboyjoseph — Tue, 04 Jan 2022 18:45:28 GMT

Thanks amitchandak but it doesn't work for me.

Re: Same period from a Specific Year

amitchandak — Thu, 06 Jan 2022 06:05:56 GMT

liboyjoseph , Please create Year a column in date table. Do not use.Year . TI does not work well with that

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

You can also try

Y3 =

var _diff = 2019 - maxx(allselected('Date'), 'Date'[Year]) )

//or var _diff = 2019 - year(maxx(allselected('Date'), 'Date'[date]) ))

return

CALCULATE([Total Sales TY],DATEADD('Date'[Date], _diff , YEAR))

Re: Same period from a Specific Year

v-xiaotang — Tue, 25 Jan 2022 10:13:34 GMT

Hi liboyjoseph

Have you solved this question with amitchandak's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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