Dear Friends,
Could any one look into my need.
I don't know where to start, but if you read the below "Data Table", hope you could understand easily. Out of the below data table I'm looking for "Percentage change over Parallal previous period". I'm writing the cases over here. And the percentage arriving formula is as below;
| ((After Value – Before Value) / Before Value) * 100 |
| Case 1 | In Detail: If an user selected January month then the result shall be;
((January 2018 - January 2017)/January 2017)*100 |
| Case 2 | If an user selected Year wise then the result shall be;
((Year 2018 - Year 2017)/Year 2017)*100 |
| Case 3 | If an user selected Q1 Quarter then the result shall be;
((Q1 2018 - Q1 2017)/Q1 2017)*100 |
| Case 4 | If an user seleted with a date then the result shall be;
((Selected date - Parallal Previous date)/Parallal Previous date)*100 |
Data Table:
| S.No | Name | Title | Date | Count |
| 1 | Brian | A1 | 7/31/2017 | 1 |
| 2 | Joss | A2 | 7/31/2017 | 1 |
| 3 | Andy | A3 | 7/31/2017 | 1 |
| 4 | Bob | B2 | 8/3/2017 | 1 |
| 5 | David | B3 | 9/15/2017 | 1 |
| 6 | Jamie | A1 | 10/13/2017 | 1 |
| 7 | Chiru | A2 | 10/13/2017 | 1 |
| 8 | Rajendra | A3 | 10/13/2017 | 1 |
| 9 | David | B1 | 11/15/2017 | 1 |
| 10 | Jamie | B2 | 11/30/2017 | 1 |
| 11 | Chiru | B3 | 12/4/2017 | 1 |
| 12 | Rajendra | B3 | 12/13/2017 | 1 |
| 13 | Chiru | A1 | 1/31/2018 | 1 |
| 14 | Rajendra | A2 | 2/5/2018 | 1 |
| 15 | David | A3 | 2/28/2018 | 1 |
| 16 | Jamie | B1 | 3/6/2018 | 1 |
| 17 | Brian | B2 | 4/11/2018 | 1 |
| 18 | Joss | B3 | 9/6/2017 | 1 |
| 19 | Andy | A1 | 9/27/2017 | 1 |
| 20 | Bob | A2 | 12/26/2017 | 1 |
| 21 | David | A3 | 12/26/2017 | 1 |
| 22 | Chiru | B1 | 8/10/2017 | 1 |
| 23 | Rajendra | B2 | 8/31/2017 | 1 |
| 24 | David | B3 | 12/4/2017 | 1 |
| 25 | Jamie | A1 | 12/13/2017 | 1 |
| 26 | Brian | A2 | 1/31/2018 | 1 |
| 27 | Joss | A3 | 2/5/2018 | 1 |
Note: The "Count " column tells you that each person got one title at a perticular date. For SUM purpose I created.
----------------------------------------------------------------------------------------------------------------
Tried DAX formula: (But failed to get the result):-
Percentage Change =
VAR CurrentSA = [Count Sum]
RETURN
IF (
ISFILTERED ( 'Date'[Date].[Date] ),
CurrentSA
- CALCULATE ( [Count Sum]- PARALLELPERIOD ( 'Date'[Date], -1, YEAR )/PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) ),
IF (
ISFILTERED ( 'Date'[Date].[Date] ),
CurrentSA
- CALCULATE ( [Count Sum]- PARALLELPERIOD ( 'Date'[Date], -1, QUARTER )/PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ) ),
IF (
ISFILTERED ( 'Date'[Date].[Date] ),
CurrentSA
- CALCULATE ( [Count Sum]- PARALLELPERIOD ( 'Date'[Date], -1, MONTH )/PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) ),
IF (
ISFILTERED ( 'Date'[Date] ),
CurrentSA
- CALCULATE ( [Count Sum], 'Date'[Date] - 1 )
)
)
)
)
--------------------------------------------------------------------------------------------------------
Your time is much more important to me aswell. Please try...Thanks
Riyaz
