Hello Exparts,
I am trying to make a growth trend analysis, but unable to get that. Issue is pritty similar to this issue.
I have a Calendar Table and Sales Table. My Sample Duration is 24 months. Example, Jan2017 till Dec2018. Calendar table contains all 730 days. My Sales Table has Daily sales data. Now I need to project a 6 months Trend Chart with MOM growth.
I need to use a Month Slicer, to select month (Jan 2018, Feb 2018 or Any) that is fixed single selection only and plot below points;
1. Selected Months Sales Volume($ value)
2. Previous Months Sales Volumn ($ Value)
3. Growth Rate (%) = (Selected Month Sales Volume - Previous Months Sales Volumn) / Previous Months Sales Volumn
4. Growth Rate Trend Line Chart > A Bar Chart to project Last 6 months Growth Rate.
I have tryed some tricks from other guru's blog,
Like,
1. Previous Months Sales Volumn ($ Value) = CALCULATE (SUM('Sales Table'[Selected Months Sales Volume]),PREVIOUSMONTH('Calendar'[Calendar Date]))
2. Growth Rate (%) =
VAR _CurrentMonthVolume = SUM('Sales Table'[Selected Months Sales Volume])
VAR _LastMonthVolume = 'Sales Table'[Previous Months Sales Volumn]
VAR _Diff = _CurrentMonthVolume - _LastMonthVolume
RETURN
DIVIDE(_Diff,_LastMonthVolume,0)
3. Growth Rate Trend = CALCULATE ('Sales Table'[Growth Rate (%)],DATESINPERIOD ( 'Calendar'[Calendar Date], MAX ( 'Calendar'[Calendar Date] ),-6, MONTH ))
Unfortunately, Nothing is working while plotting these No. in a table. Growth Trend is showing all 100% and 0%. but in Card all Numbers are comming correct.
Below is the Sample Pipe delimited ( | ) Date
Cal Date|Branch|Sales No|Sales Volumn
=========================
2018-01-31|BRN01|63|1585
2018-02-28|BRN01|66|1645
2018-03-31|BRN01|60|1706
2018-04-30|BRN01|57|1760
2018-05-31|BRN01|61|1814
2018-06-30|BRN01|66|1871
2018-07-31|BRN01|73|1931
2018-08-31|BRN01|78|1997
2018-09-30|BRN01|80|2070
2018-10-31|BRN01|72|2142
2018-11-30|BRN01|80|2209
2018-12-31|BRN01|77|2283
2018-01-31|BRN02|54|1690
2018-02-28|BRN02|57|1739
2018-03-31|BRN02|55|1792
2018-04-30|BRN02|61|1842
2018-05-31|BRN02|64|1899
2018-06-30|BRN02|68|1959
2018-07-31|BRN02|62|2022
2018-08-31|BRN02|60|2080
2018-09-30|BRN02|61|2135
2018-10-31|BRN02|59|2191
2018-11-30|BRN02|57|2245
2018-12-31|BRN02|55|2299
2018-01-31|BRN03|86|1716
2018-02-28|BRN03|84|1795
2018-03-31|BRN03|89|1873
2018-04-30|BRN03|80|1956
2018-05-31|BRN03|73|2030
2018-06-30|BRN03|81|2097
2018-07-31|BRN03|86|2170
2018-08-31|BRN03|81|2251
2018-09-30|BRN03|75|2327
2018-10-31|BRN03|71|2397
2018-11-30|BRN03|76|2462
2018-12-31|BRN03|80|2532
2018-01-31|BRN04|63|1657
2018-02-28|BRN04|69|1715
2018-03-31|BRN04|70|1779
2018-04-30|BRN04|77|1842
2018-05-31|BRN04|84|1915
2018-06-30|BRN04|76|1993
2018-07-31|BRN04|75|2062
2018-08-31|BRN04|76|2130
2018-09-30|BRN04|83|2199
2018-10-31|BRN04|79|2277
2018-11-30|BRN04|75|2349
2018-12-31|BRN04|72|2419
2018-01-31|BRN05|74|1765
2018-02-28|BRN05|81|1833
2018-03-31|BRN05|85|1908
2018-04-30|BRN05|90|1985
2018-05-31|BRN05|96|2069
2018-06-30|BRN05|91|2158
2018-07-31|BRN05|91|2242
2018-08-31|BRN05|89|2326
2018-09-30|BRN05|80|2410
2018-10-31|BRN05|81|2486
2018-11-30|BRN05|76|2559
2018-12-31|BRN05|74|2629
Would be greatful if any of you can help me on this.
Regards,
Sukhomoy
