Forum Discussion

alexcatala's avatar
alexcatala
Icon for Helper IV rankHelper IV
5 years ago
Solved

select multiple values to compare LY vs TY

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

Need Help
  • amitchandak's avatar
    amitchandak
    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      alexcatala
      Icon for Helper IV rankHelper IV
      5 years ago

      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.