Problem with 12 months Trend Calculating (Date Hierarchy)

[jurkowskij]

I'm trying to figure out a way to use 12 month trend with date hierarchy

Formula I use

Trend 12 M = 
CALCULATE( 
    [Qty]
    ,DATESINPERIOD(Sales[OrderDate],max(Sales[OrderDate]),-12,MONTH)
    )

At first look this formula works perfectly day by day:

But when I use date hierarchy this happen:

So now trend are equal to Qty

How to solve this problem?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[jurkowskij]

This is my source date:

Date		Qty
31.05.2011		820
01.07.2011		2053
01.08.2011		1512
31.08.2011		1242
01.10.2011		2963
31.10.2011		2245
01.12.2011		852
01.01.2012		2132
29.01.2012		1642
29.02.2012		1260
30.03.2012		2965
30.04.2012		2204
30.05.2012		7502
30.06.2012		11044
31.07.2012		8868
30.08.2012		5355
30.09.2012		8075
30.10.2012		6342
30.11.2012		3288

Now I add to table my measure:

Date		Qty		Trend 12 M
31.05.2011		820		820
01.07.2011		2053		2873
01.08.2011		1512		4385
31.08.2011		1242		5627
01.10.2011		2963		8590
31.10.2011		2245		10835
01.12.2011		852		11687
01.01.2012		2132		13819
29.01.2012		1642		15461
29.02.2012		1260		16721
30.03.2012		2965		19686
30.04.2012		2204		21890
30.05.2012		7502		28572
30.06.2012		11044		39616
31.07.2012		8868		46431
30.08.2012		5355		49032
30.09.2012		8075		57107
30.10.2012		6342		58241
30.11.2012		3288		61529

When I change Date to Date Hierarchy this happen:

Now Qty equals Trend

Year	Quarter	Month	Day	Qty	Trend 12 M
2011	Qtr 2	May	31	820	820
2011	Qtr 3	July	1	2053	2053
2011	Qtr 3	August	1	1512	1512
2011	Qtr 3	August	31	1242	1242
2011	Qtr 4	October	1	2963	2963
2011	Qtr 4	October	31	2245	2245
2011	Qtr 4	December	1	852	852
2012	Qtr 1	January	1	2132	2132
2012	Qtr 1	January	29	1642	1642
2012	Qtr 1	February	29	1260	1260
2012	Qtr 1	March	30	2965	2965
2012	Qtr 2	April	30	2204	2204
2012	Qtr 2	May	30	7502	7502
2012	Qtr 2	June	30	11044	11044
2012	Qtr 3	July	31	8868	8868
2012	Qtr 3	August	30	5355	5355
2012	Qtr 3	September	30	8075	8075
2012	Qtr 4	October	30	6342	6342
2012	Qtr 4	November	30	3288	3288

At the moment everything is calculated on one source table ("Sales").

I noticed that if I create a calendar on an external table and make a relationship with it

and slightly change the formula of the measure, somehow it starts to work correctly

Trend 12 M Calendar = 
    CALCULATE(
    sum('Sales'[Qty]),
    DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-12,MONTH)
    )

Year	Quarter	Month	Day	Qty	Trend 12 M Calendar
2011	Qtr 2	May	31	820	820
2011	Qtr 3	July	1	2053	2873
2011	Qtr 3	August	1	1512	4385
2011	Qtr 3	August	31	1242	5627
2011	Qtr 4	October	1	2963	8590
2011	Qtr 4	October	31	2245	10835
2011	Qtr 4	December	1	852	11687
2012	Qtr 1	January	1	2132	13819
2012	Qtr 1	January	29	1642	15461
2012	Qtr 1	February	29	1260	16721
2012	Qtr 1	March	30	2965	19686
2012	Qtr 2	April	30	2204	21890
2012	Qtr 2	May	30	7502	29392
2012	Qtr 2	June	30	11044	39616
2012	Qtr 3	July	31	8868	46431
2012	Qtr 3	August	30	5355	50274
2012	Qtr 3	September	30	8075	57107
2012	Qtr 4	October	30	6342	60486
2012	Qtr 4	November	30	3288	61529

Please explain to me why the formula doesn't work in the first case but in the second it does

[lbendlin · Accepted solution]

Your data model needs some changes. The Calendar table is a dimension table and needs to have a 1:*, single direction relationship to your fact (Sales) table. 

Next step is to use the date from the dimension, not from the fact. Then your calculations will work.

see attached

[jurkowskij]

Ohhh now I understand.
Thanks a lot