Forum Discussion

jurkowskij's avatar
jurkowskij
Frequent Visitor
3 years ago
Solved

Problem with 12 months Trend Calculating (Date Hierarchy)

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) ...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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's avatar
jurkowskij
Frequent Visitor
3 years ago

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

 

YearQuarterMonthDayQtyTrend 12 M
2011Qtr 2May31820820
2011Qtr 3July120532053
2011Qtr 3August115121512
2011Qtr 3August3112421242
2011Qtr 4October129632963
2011Qtr 4October3122452245
2011Qtr 4December1852852
2012Qtr 1January121322132
2012Qtr 1January2916421642
2012Qtr 1February2912601260
2012Qtr 1March3029652965
2012Qtr 2April3022042204
2012Qtr 2May3075027502
2012Qtr 2June301104411044
2012Qtr 3July3188688868
2012Qtr 3August3053555355
2012Qtr 3September3080758075
2012Qtr 4October3063426342
2012Qtr 4November3032883288


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

 

 

YearQuarterMonthDayQtyTrend 12 M Calendar
2011Qtr 2May31820820
2011Qtr 3July120532873
2011Qtr 3August115124385
2011Qtr 3August3112425627
2011Qtr 4October129638590
2011Qtr 4October31224510835
2011Qtr 4December185211687
2012Qtr 1January1213213819
2012Qtr 1January29164215461
2012Qtr 1February29126016721
2012Qtr 1March30296519686
2012Qtr 2April30220421890
2012Qtr 2May30750229392
2012Qtr 2June301104439616
2012Qtr 3July31886846431
2012Qtr 3August30535550274
2012Qtr 3September30807557107
2012Qtr 4October30634260486
2012Qtr 4November30328861529

 

 

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

 

 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      jurkowskij
      Frequent Visitor
      3 years ago

      Ohhh now I understand.
      Thanks a lot