Compare 2 equivalent periods without date

[fabienrolland]

Hi,

I have a Marketing Cycle Calendar that shows for each date, the corresponding cycle year, cycle name, cycle number, etc. See below

DATE	CYCLE_NB	CYCLE_NAME	CYCLE_DAY	CYCLE_WEEK	CYCLE_YEAR
25/12/2017	01	Soldes Hiver	1	1	2018
26/12/2017	01	Soldes Hiver	2	1	2018
27/12/2017	01	Soldes Hiver	3	1	2018
28/12/2017	01	Soldes Hiver	4	1	2018
29/12/2017	01	Soldes Hiver	5	1	2018
30/12/2017	01	Soldes Hiver	6	1	2018
31/12/2017	01	Soldes Hiver	7	1	2018
01/01/2018	01	Soldes Hiver	8	2	2018
02/01/2018	01	Soldes Hiver	9	2	2018
03/01/2018	01	Soldes Hiver	10	2	2018
04/01/2018	01	Soldes Hiver	11	2	2018
05/01/2018	01	Soldes Hiver	12	2	2018
06/01/2018	01	Soldes Hiver	13	2	2018
07/01/2018	01	Soldes Hiver	14	2	2018
08/01/2018	01	Soldes Hiver	15	3	2018
09/01/2018	01	Soldes Hiver	16	3	2018
10/01/2018	01	Soldes Hiver	17	3	2018
11/01/2018	01	Soldes Hiver	18	3	2018
12/01/2018	01	Soldes Hiver	19	3	2018
13/01/2018	01	Soldes Hiver	20	3	2018
14/01/2018	01	Soldes Hiver	21	3	2018
15/01/2018	01	Soldes Hiver	22	4	2018
16/01/2018	01	Soldes Hiver	23	4	2018
17/01/2018	01	Soldes Hiver	24	4	2018
18/01/2018	01	Soldes Hiver	25	4	2018
19/01/2018	01	Soldes Hiver	26	4	2018
20/01/2018	01	Soldes Hiver	27	4	2018

The CYCLE_CALENDAR is linked to the main Calendar table which is linked to the fact tables. 

One of the fact tables (SALE_HEADER) includes Sales amount.

I am trying to calculate the Sales amount corresponding to the same period last year.

CA YR PY (Cycle equivalent dates) =
CALCULATE(
SUM(SALE_HEADER[SALES AMOUNT]);
FILTER(ALL(CYCLE_CALENDAR);
CYCLE_CALENDAR[CYCLE_YEAR] = MIN(CYCLE_CALENDAR[CYCLE_YEAR]) - 1 &&
CYCLE_CALENDAR[CYCLE_NB] = MIN(CYCLE_CALENDAR[CYCLE_NB]) &&
CYCLE_CALENDAR[CYCLE_DAY] = MIN(CYCLE_CALENDAR[CYCLE_DAY])
)
)

It works at the CYCLE_DAY level, but when i Agregate by  month or year i get the value corresponding to the smallest date (Probably because i used the MIN() in my DAX function).

How can i solve this? Thanks a million

Sorry i couldnt copy paste a print screen

Année	Trimestre	Mois	Jour	CA YR	CA YR PY (Cycle equivalent dates)
2018	Trim 1	février	14	$87790	$48938
2018	Trim 1	février	18	$74676	$67662
2018	Trim 1	février	15	$88417	$73797
2018	Trim 1	février	17	$101244	$95656
2018	Trim 1	février	16	$101958	$104137
Total février				$454085	$48938
Total 2018				$454086	$48939

[v-chuncz-msft]

@fabienrolland,

You may take a look at https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/.