Forum Discussion
Problem with 12 months Trend Calculating (Date Hierarchy)
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
Super UserPlease 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-p/963216
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
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
Ohhh now I understand.
Thanks a lot
