topic Re: Sales of Closest Date Last Year in DAX Commands and Tips

Sales of Closest Date Last Year

Flpowerbi01 — Thu, 08 Feb 2024 10:33:14 GMT

Hi all,

I am trying to get the closest sales last year to a date in a row. Please see below of what I want to achieve,

Here are my columns

Date - On sale Date of a product

Sales - Sales of the product on the on sale date

Date minus 365 - Date is subtracted by a year

Sales of Closest Date Last Year - This is the column I am trying to get. How do I get the sales of the date that is closest to the "Date minus 365 column". I manually filled out some of the values of the colmun. I cannot use a max or min function for cases when the difference between the two dates is less than 365.

date Sales Date minus 365 Closest Date Last Year Sales

11/11/22	9624	11/11/21
18/11/22	8151	18/11/21
25/11/22	7110	25/11/21
02/12/22	8280	02/12/21
09/12/22	5206	09/12/21
23/12/22	6603	23/12/21
06/01/23	7786	06/01/22
13/01/23	9519	13/01/22
20/01/23	8525	20/01/22
27/01/23	6680	27/01/22
03/02/23	5020	03/02/22
10/02/23	6507	10/02/22
17/02/23	5365	17/02/22
24/02/23	8347	24/02/22
03/03/23	5070	03/03/22
10/03/23	7377	10/03/22
17/03/23	6519	17/03/22
24/03/23	8206	24/03/22
31/03/23	7893	31/03/22
07/04/23	8459	07/04/22
14/04/23	5832	14/04/22
21/04/23	7881	21/04/22
28/04/23	6453	28/04/22
05/05/23	6158	05/05/22
12/05/23	7916	12/05/22
19/05/23	9214	19/05/22
26/05/23	9912	26/05/22
02/06/23	9522	02/06/22
09/06/23	9637	09/06/22
16/06/23	5817	16/06/22
23/06/23	9671	23/06/22
30/06/23	7480	30/06/22
07/07/23	9366	07/07/22
14/07/23	8169	14/07/22
21/07/23	8952	21/07/22
28/07/23	6390	28/07/22
04/08/23	5669	04/08/22
11/08/23	6408	11/08/22
18/08/23	5373	18/08/22
25/08/23	6678	25/08/22
01/09/23	9055	01/09/22
08/09/23	6189	08/09/22
15/09/23	9569	15/09/22
22/09/23	5256	22/09/22
29/09/23	9812	29/09/22
06/10/23	8727	06/10/22
13/10/23	8446	13/10/22
20/10/23	8936	20/10/22
27/10/23	5396	27/10/22
03/11/23	9032	03/11/22
10/11/23	9636	10/11/22
17/11/23	7647	17/11/22
24/11/23	7758	24/11/22
01/12/23	9654	01/12/22
08/12/23	9504	08/12/22	9624
22/12/23	9907	22/12/22	7110
05/01/24	7919	05/01/23	7786
12/01/24	8506	12/01/23	9519
19/01/24	5042	19/01/23	8525
26/01/24	8195	26/01/23	6680
02/02/24	6903	02/02/23	5020

Re: Sales of Closest Date Last Year

talespin — Thu, 08 Feb 2024 11:56:21 GMT

hi Flpowerbi01

Closest Sales before of after Date minus 365?

If Yes, this is a Calculated Column

Closest Sales =

VAR _CurrMinus365Dt = TestTbl6[Date minus 365]

VAR _Cross = CROSSJOIN( ALL(TestTbl6[date], TestTbl6[Sales]), FILTER(ALL(TestTbl6[Date minus 365]), [Date minus 365] = _CurrMinus365Dt))

VAR _Tbl = ADDCOLUMNS(_Cross, "@Difference", ABS(INT([date]-[Date minus 365])))

VAR _MinDiff = MINX( _Tbl, [@Difference])

RETURN SELECTCOLUMNS( FILTER( _Tbl, [@Difference] = _MinDiff), "NewCol", [Sales])

Re: Sales of Closest Date Last Year

Flpowerbi01 — Thu, 08 Feb 2024 12:12:01 GMT

Hi talespin ,

thank you so much for this. If there was also a product column in the table. Where will I put an additional filter for the product colum in your Dax code?

SInce what is happeneing now is that it is getting the closest date even if it is not that specific product? Does that make sense? Or I can give you another example with the product

Re: Sales of Closest Date Last Year

talespin — Thu, 08 Feb 2024 12:26:28 GMT

hi Flpowerbi01 ,

Please test it.

Added Product to first table in crossjoin.

Closest Sales =

VAR _CurrMinus365Dt = TestTbl6[Date minus 365]

VAR _Product = TestTbl6[Product]

VAR _Cross = CROSSJOIN( FILTER( ALL(TestTbl6[date], TestTbl6[Sales], TestTbl6[Product]), [Product] = _Product ), FILTER(ALL(TestTbl6[Date minus 365]), [Date minus 365] = _CurrMinus365Dt))

VAR _Tbl = ADDCOLUMNS(_Cross, "@Difference", ABS(INT([date]-[Date minus 365])))

VAR _MinDiff = MINX( _Tbl, [@Difference])

RETURN SELECTCOLUMNS( FILTER( _Tbl, [@Difference] = _MinDiff), "NewCol", [Sales])

Re: Sales of Closest Date Last Year

Flpowerbi01 — Thu, 08 Feb 2024 14:23:02 GMT

Thank you so much Talespin!

Re: Sales of Closest Date Last Year

talespin — Thu, 08 Feb 2024 16:05:05 GMT

You're welcome.