Hi All,
Need help for the below mentioned scenario. I treied one solution but couldnt resolve formatting for each columns as expected and sorting acc. to Expected result, along with Percentage column in one matrix:
Need to calculate This year, Last Year and Difference TY-LY from Table daily load based on Timeline slicer selection.
Few columns are calculated between startdate and enddate, few based on only startdate and few calculation based on only end date. Thanks in advance!
| Need help for the below mentioned sample data :data loaded from 2011 till yesterday date for each day for each location | | |
| startdate | salesIn | salesoff | Wrieoff | Ft | NetRent | Locationid |
| 4/1/2019 | 10 | 2 | 0.5 | $200 | $200,000 | 1 |
| 4/2/2019 | 8 | 0 | 0.7 | $201 | $201,000 | 1 |
| 4/3/2019 | 7 | 1 | 1 | $202 | $202,000 | 1 |
| 4/4/2019 | 5 | 0 | 0.5 | $203 | $203,000 | 1 |
| 4/5/2019 | 1 | 2 | 0.9 | $204 | $204,000 | 1 |
| 4/1/2019 | 9 | 4 | 0.8 | $205 | $205,000 | 2 |
| 4/2/2019 | 2 | 3 | 0.8 | $206 | $206,000 | 2 |
| 4/3/2019 | 8 | 0 | 0.8 | $207 | $207,000 | 2 |
| 4/4/2019 | 4 | 1 | 0.8 | $208 | $208,000 | 2 |
| 4/5/2019 | 3 | 1 | 0.8 | $209 | $209,000 | 2 |
| 4/1/2018 | 10 | 2 | 0.8 | $210 | $210,000 | 1 |
| 4/2/2018 | 15 | 5 | 0.8 | $211 | $211,000 | 1 |
| 4/3/2018 | 7 | 1 | 0.8 | $212 | $212,000 | 1 |
| 4/4/2018 | 6 | 0 | 0.8 | $213 | $213,000 | 1 |
| 4/5/2018 | 3 | 1 | 0.8 | $214 | $214,000 | 1 |
| 4/1/2018 | 1 | 2 | 0.8 | $215 | $215,000 | 2 |
| 4/2/2018 | 12 | 7 | 0.8 | $216 | $216,000 | 2 |
| 4/3/2018 | 3 | 2 | 0.8 | $217 | $217,000 | 2 |
| 4/4/2018 | 4 | 0 | 0.8 | $218 | $218,000 | 2 |
| 4/5/2018 | 2 | 2 | 0.8 | $219 | $219,000 | 2 |
| Report contain 2 slicer Timelineslicer and LocationID Slicer | | | | |
| startdate will always be first of that month | | | | |
| PercentageOcc =sum salein between startdate and enddate / writeoff basedon timelineslicer end date value | Per_NetRent=sum(NetRent) on First of seleceted month /sales in between startdate and enddate |
| Please help in how to calculate actual , LY and difference column based on timeline slicer selection and how to define data in category actual ,LY,difference | |
| |
| Please help how to represent the data in the form of matrix table which will show data on rows as shown in example | |
| Timesline Slicer contain | | | | | | |
| EndDate for Timeline Slicer | | | | | | | |
| 4/4/2019 | For Ex User selected 4/4/19 | | | | | | |
| Location ID :ALL | | | | | | |
| Expected Results | | |
| Attribute | Actual | LY | Difference | | | | |
| salesoff | 11 | 19 | -8 | -ve red , no need $sign | calculation based on b/w startdate and enddate | | |
| salesIn | 53 | 58 | -5 | -ve red ,no need $sign | calculation based on b/w startdate and enddate | | |
| NetRent | $1,632,000 | $1,712,000 | ($80,000) | -ve red ,need $sign , no decimal place | calculation based on b/w startdate and enddate | | |
| Writeoff | 1.3 | 3.2 | -1.9 | -ve red , one decimalplace | calculation based on enddate | | |
| PerOcc | 40.80% | 44.60% | -3.80% | need -ve red , %sign ,with 1 decimal as part of 1 matrix | calculation based on b/w startdate and enddate | | |
| Ft | $411 | $431 | ($20) | -ve red, nodecimal place but $sign | calculation based on enddate | | |
| Per_NetRent | $7642 | $7328 | $314 | need -ve red , %sign ,with 1 decimal as part of 1 matrix | calculation based on 1st of the month (based on end date month selection ) | | |
| sorting attribute in user requested order there is no asc or desc criteria | | |
Thanks, Neha
