select multiple values to compare LY vs TY

[alexcatala]

Hi, recently I have been working in a formula to extract the comparison between TY vs LY :

Unfortunately, the formula I selected only extra a unique value, which if it is applicable to an only store it works, but doesn't work when it is applied for a whole district. 

Any suggestion who to select multiple values from LY sales and compare them within this year by a district as well.

Sales (D) vs LY (LFL-D) =

VAR TotalPriorYearSales =

SUMX (

d_StoresAuto,

VAR StoreClosingDate =

CALCULATE ( MIN ( d_StoresAuto[ClosingDate] ) )

VAR StoreClosingDate_PastYear =

SELECTEDVALUE(d_StoresAuto[ClosingDate],Today()) - 365

RETURN

IF (

ISBLANK ( StoreClosingDate ),

CALCULATE ( [Sales (D)], SAMEPERIODLASTYEAR ( d_DateTable[Date] ) ),

CALCULATE (

CALCULATE ( [Sales (D)], SAMEPERIODLASTYEAR ( d_DateTable[Date] ) ),

f_DailySales[Date] < StoreClosingDate

)

)

)

VAR TotalCurrentYearSales =

SUMX (

d_StoresAuto,

VAR StoreOpeningDate =

CALCULATE ( MIN ( d_StoresAuto[OpeningDate] ) )

VAR StoreOpeningDate_NextYear =

SELECTEDVALUE(d_StoresAuto[OpeningDate],TODAY()) +365

RETURN

CALCULATE ( [Sales (D)], f_DailySales[Date] >= StoreOpeningDate_NextYear )

)

VAR Result =

DIVIDE ( TotalCurrentYearSales - TotalPriorYearSales, TotalPriorYearSales )

RETURN

IF ( Result = 0, [ND_Sign], Result )

I have used selectvalue, and it works perfectly with only 1 value ( 1 store) but doesn't work when I select a whole district.

Any idea?

Thanks in advance

[Anonymous]

@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.

[alexcatala]

@Anonymous 

I have tried with CONCATENATEX( VALUES()) and doesn't show me any result. 

CONCATENATEX(values(d_StoresAuto[OpeningDate]),TODAY()) +365

Due 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.

[amitchandak · Accepted solution]

@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.