how to solve DATE issue in formula Sales vs Sales LY

[alexcatala]

Recently the formula I had to measure TY sales compared to LY it doesn't work.

It shows me an automatic message saying: An argument with of function "DATE" has the wrong date type.

 

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

VAR TotalPriorYearSales =

SUMX (

d_StoresAuto,

VAR StoreClosingDate =

CALCULATE ( MIN ( d_StoresAuto[ClosingDate] ) )

VAR StoreClosingDate_PastYear =

DATE ( YEAR ( StoreClosingDate ) - 1, MONTH ( StoreClosingDate ), DAY ( StoreClosingDate ) )

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 =

DATE ( YEAR ( StoreOpeningDate ) + 1, MONTH ( StoreOpeningDate ), DAY ( StoreOpeningDate ) )

RETURN

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

)

VAR Result =

DIVIDE ( TotalCurrentYearSales - TotalPriorYearSales, TotalPriorYearSales )

RETURN

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

How can I change the step of DATE in the formula?

DATE ( YEAR ( StoreOpeningDate ) + 1, MONTH ( StoreOpeningDate ), DAY ( StoreOpeningDate ) )

Any suggestion?

[amitchandak · Accepted solution]

@alexcatala ,This seem correct -DATE ( YEAR ( StoreClosingDate ) - 1, MONTH ( StoreClosingDate ), DAY ( StoreClosingDate ) )

Create a date table try formula's like the example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date))

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

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]

Hi,

@amitchandak  I have tried with multiple of them and they are not accurate, it is also needed the next year into the formula. The way I tested:

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

VAR TotalPriorYearSales =

SUMX (

d_StoresAuto,

VAR StoreClosingDate =

CALCULATE ( MIN ( d_StoresAuto[ClosingDate] ) )

VAR StoreClosingDate_PastYear =

CALCULATE(SUMX(f_DailySales,[Sales (D)]),dateadd(d_StoresAuto[ClosingDate],-1,Year))

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 =

CALCULATE(SUMX(f_DailySales,[Sales (D)]),dateadd(d_StoresAuto[OpeningDate],+1,Year))

RETURN

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

)

VAR Result =

DIVIDE ( TotalCurrentYearSales - TotalPriorYearSales, TotalPriorYearSales )

RETURN

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

Let me know if you see anything that can be done differently.

Thanks