Forum Discussion
select multiple values to compare LY vs TY
- 5 years ago
alexcatala , have tried datesytd with date table
Examples. Try 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")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) //Only year vs Year, not a level below This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1)) rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) ) diff = [This Year]-[Last Year ] diff % = divide([This Year]-[Last Year ],[Last Year ]) column Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense) measure This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]))) Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))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 :radacad sqlbi My Video Series Appreciate your Kudos.
alexcatala
The formula seems messy for me with many var and returns, like what is d_StoresAuto measure, we don't have your model so difficult to troubleshoot your formula or rewrite TY and LY measures.
However, for multi-selection, instead of selectedvalue you can use CONCATENATEX ( VALUES()), check this post for detail:
Solved: display multiple elements with SELECTEDVALUE - Microsoft Power BI Community
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
- alexcatala5 years ago
Helper IV
Anonymous
I have tried with CONCATENATEX( VALUES()) and doesn't show me any result.
CONCATENATEX(values(d_StoresAuto[OpeningDate]),TODAY()) +365Due I want to display the comparison between year how should I formulate this formula to make it for the previous and next year?I have checked the link but doesn't answer this question in full.Thanks in advance for your support.