Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have two dates in a single table called history , scenario date and reporting date and have values of salary proposed tied to it. I have another table called current having report date and actual salary tied to it. I want to know 3 months back of current table report date that how much salary was proposed for that month and what is the difference between these two values on a monthly level. In that way i can do the salary forecast error and accuracy. The relationship between these tables are through bridge table of employee. Please help me out finding it, I am putting a sample data in excel.
Thank You
Hi @Anonymous ,
What is your data structure for your HISTORY TABLE and does below picture show all the data in your CURRENT TABLE?
Could you please post your HISTORY TABLE data in a tbale(not matrix) and post your desired result that I could test for you? Or you could just upload a pbix file to have a test.
Regards,
Daniel He
Hi Daniel,
I can not give you actual data but giving you a similar data . I am not able to post an excel so putting data here, if you know any way to upload a file , please let me know, I am new to the community.
Thanks
Scenario_Date | Employee ID | Proposed Salary | SDATE |
8/1/2018 0:00 | 1 | 9,210 | 1/1/2019 0:00 |
8/1/2018 0:00 | 1 | 54,970 | 2/2/2019 0:00 |
8/1/2018 0:00 | 1 | 52,560 | 3/13/2019 0:00 |
8/1/2018 0:00 | 1 | 27,360 | 4/18/2018 0:00 |
8/1/2018 0:00 | 1 | 36,480 | 3/25/2018 0:00 |
8/1/2018 0:00 | 1 | 20,490 | 5/1/2018 0:00 |
8/1/2018 0:00 | 1 | 45,600 | 6/8/2018 0:00 |
8/1/2018 0:00 | 1 | 33,120 | 7/15/2018 0:00 |
8/1/2018 0:00 | 1 | 27,360 | 8/22/2018 0:00 |
8/1/2018 0:00 | 1 | 15,600 | 9/1/2018 0:00 |
8/1/2018 0:00 | 1 | 46,200 | 10/6/2018 0:00 |
8/1/2018 0:00 | 1 | 42,690 | 11/13/2019 0:00 |
8/1/2018 0:00 | 1 | 36,480 | 12/20/2018 0:00 |
9/1/2018 0:00 | 1 | 6,480 | 1/1/2019 0:00 |
9/1/2018 0:00 | 1 | 64,740 | 2/2/2019 0:00 |
9/1/2018 0:00 | 1 | 48,650 | 3/13/2019 0:00 |
9/1/2018 0:00 | 1 | 45,600 | 4/18/2018 0:00 |
9/1/2018 0:00 | 1 | 15,600 | 3/25/2018 0:00 |
9/1/2018 0:00 | 1 | 38,880 | 5/1/2018 0:00 |
9/1/2018 0:00 | 1 | 76,810 | 6/8/2018 0:00 |
9/1/2018 0:00 | 1 | 36,480 | 7/15/2018 0:00 |
9/1/2018 0:00 | 1 | 9,120 | 8/22/2018 0:00 |
9/1/2018 0:00 | 1 | 1,920 | 9/1/2018 0:00 |
9/1/2018 0:00 | 1 | 48,000 | 10/6/2018 0:00 |
9/1/2018 0:00 | 1 | 59,170 | 11/13/2019 0:00 |
9/1/2018 0:00 | 1 | 57,170 | 12/20/2018 0:00 |
10/1/2018 0:00 | 3 | 6,480 | 1/1/2019 0:00 |
10/1/2018 0:00 | 3 | 64,740 | 2/2/2019 0:00 |
10/1/2018 0:00 | 3 | 48,650 | 3/13/2019 0:00 |
10/1/2018 0:00 | 3 | 45,600 | 4/18/2018 0:00 |
10/1/2018 0:00 | 3 | 15,600 | 3/25/2018 0:00 |
10/1/2018 0:00 | 3 | 38,880 | 5/1/2018 0:00 |
10/1/2018 0:00 | 3 | 54,970 | 6/8/2018 0:00 |
10/1/2018 0:00 | 3 | 52,560 | 7/15/2018 0:00 |
10/1/2018 0:00 | 3 | 27,360 | 8/22/2018 0:00 |
10/1/2018 0:00 | 3 | 36,480 | 9/1/2018 0:00 |
10/1/2018 0:00 | 3 | 9,120 | 10/6/2018 0:00 |
10/1/2018 0:00 | 3 | 1,920 | 11/13/2019 0:00 |
10/1/2018 0:00 | 3 | 48,000 | 12/20/2018 0:00 |
8/1/2018 0:00 | 2 | 9,210 | 1/1/2019 0:00 |
8/1/2018 0:00 | 2 | 54,970 | 2/2/2019 0:00 |
8/1/2018 0:00 | 2 | 52,560 | 3/13/2019 0:00 |
8/1/2018 0:00 | 2 | 27,360 | 4/18/2018 0:00 |
8/1/2018 0:00 | 2 | 36,480 | 3/25/2018 0:00 |
8/1/2018 0:00 | 2 | 20,490 | 5/1/2018 0:00 |
8/1/2018 0:00 | 2 | 45,600 | 6/8/2018 0:00 |
8/1/2018 0:00 | 2 | 33,120 | 7/15/2018 0:00 |
8/1/2018 0:00 | 2 | 27,360 | 8/22/2018 0:00 |
8/1/2018 0:00 | 2 | 15,600 | 9/1/2018 0:00 |
8/1/2018 0:00 | 2 | 46,200 | 10/6/2018 0:00 |
8/1/2018 0:00 | 2 | 42,690 | 11/13/2019 0:00 |
8/1/2018 0:00 | 2 | 36,480 | 12/20/2018 0:00 |
9/1/2018 0:00 | 2 | 6,480 | 1/1/2019 0:00 |
9/1/2018 0:00 | 2 | 64,740 | 2/2/2019 0:00 |
9/1/2018 0:00 | 2 | 48,650 | 3/13/2019 0:00 |
9/1/2018 0:00 | 2 | 45,600 | 4/18/2018 0:00 |
9/1/2018 0:00 | 2 | 15,600 | 3/25/2018 0:00 |
9/1/2018 0:00 | 2 | 38,880 | 5/1/2018 0:00 |
9/1/2018 0:00 | 2 | 76,810 | 6/8/2018 0:00 |
9/1/2018 0:00 | 2 | 36,480 | 7/15/2018 0:00 |
9/1/2018 0:00 | 2 | 9,120 | 8/22/2018 0:00 |
9/1/2018 0:00 | 2 | 1,920 | 9/1/2018 0:00 |
9/1/2018 0:00 | 2 | 48,000 | 10/6/2018 0:00 |
9/1/2018 0:00 | 2 | 59,170 | 11/13/2019 0:00 |
9/1/2018 0:00 | 2 | 57,170 | 12/20/2018 0:00 |
10/1/2018 0:00 | 2 | 6,480 | 1/1/2019 0:00 |
10/1/2018 0:00 | 2 | 64,740 | 2/2/2019 0:00 |
10/1/2018 0:00 | 2 | 48,650 | 3/13/2019 0:00 |
10/1/2018 0:00 | 2 | 45,600 | 4/18/2018 0:00 |
10/1/2018 0:00 | 2 | 15,600 | 3/25/2018 0:00 |
10/1/2018 0:00 | 2 | 38,880 | 5/1/2018 0:00 |
10/1/2018 0:00 | 2 | 54,970 | 6/8/2018 0:00 |
10/1/2018 0:00 | 2 | 52,560 | 7/15/2018 0:00 |
10/1/2018 0:00 | 2 | 27,360 | 8/22/2018 0:00 |
10/1/2018 0:00 | 2 | 36,480 | 9/1/2018 0:00 |
10/1/2018 0:00 | 2 | 9,120 | 10/6/2018 0:00 |
10/1/2018 0:00 | 2 | 1,920 | 11/13/2019 0:00 |
10/1/2018 0:00 | 2 | 41,000 | 12/20/2018 0:00 |
8/1/2018 0:00 | 3 | 9,210 | 1/1/2019 0:00 |
8/1/2018 0:00 | 3 | 54,970 | 2/2/2019 0:00 |
8/1/2018 0:00 | 3 | 52,560 | 3/13/2019 0:00 |
8/1/2018 0:00 | 3 | 27,360 | 4/18/2018 0:00 |
8/1/2018 0:00 | 3 | 36,480 | 3/25/2018 0:00 |
8/1/2018 0:00 | 3 | 20,490 | 5/1/2018 0:00 |
8/1/2018 0:00 | 3 | 45,600 | 6/8/2018 0:00 |
8/1/2018 0:00 | 3 | 33,120 | 7/15/2018 0:00 |
8/1/2018 0:00 | 3 | 27,360 | 8/22/2018 0:00 |
8/1/2018 0:00 | 3 | 15,600 | 9/1/2018 0:00 |
8/1/2018 0:00 | 3 | 46,200 | 10/6/2018 0:00 |
8/1/2018 0:00 | 3 | 42,690 | 11/13/2019 0:00 |
8/1/2018 0:00 | 3 | 36,480 | 12/20/2018 0:00 |
9/1/2018 0:00 | 3 | 6,480 | 1/1/2019 0:00 |
9/1/2018 0:00 | 3 | 64,740 | 2/2/2019 0:00 |
9/1/2018 0:00 | 3 | 48,650 | 3/13/2019 0:00 |
9/1/2018 0:00 | 3 | 45,600 | 4/18/2018 0:00 |
9/1/2018 0:00 | 3 | 15,600 | 3/25/2018 0:00 |
9/1/2018 0:00 | 3 | 38,880 | 5/1/2018 0:00 |
9/1/2018 0:00 | 3 | 76,810 | 6/8/2018 0:00 |
9/1/2018 0:00 | 3 | 36,480 | 7/15/2018 0:00 |
9/1/2018 0:00 | 3 | 9,120 | 8/22/2018 0:00 |
9/1/2018 0:00 | 3 | 1,920 | 9/1/2018 0:00 |
9/1/2018 0:00 | 3 | 48,000 | 10/6/2018 0:00 |
9/1/2018 0:00 | 3 | 59,170 | 11/13/2019 0:00 |
9/1/2018 0:00 | 3 | 57,170 | 12/20/2018 0:00 |
10/1/2018 0:00 | 3 | 6,480 | 1/1/2019 0:00 |
10/1/2018 0:00 | 3 | 64,740 | 2/2/2019 0:00 |
10/1/2018 0:00 | 3 | 48,650 | 3/13/2019 0:00 |
10/1/2018 0:00 | 3 | 45,600 | 4/18/2018 0:00 |
10/1/2018 0:00 | 3 | 15,600 | 3/25/2018 0:00 |
10/1/2018 0:00 | 3 | 38,880 | 5/1/2018 0:00 |
10/1/2018 0:00 | 3 | 54,970 | 6/8/2018 0:00 |
10/1/2018 0:00 | 3 | 52,560 | 7/15/2018 0:00 |
10/1/2018 0:00 | 3 | 27,360 | 8/22/2018 0:00 |
10/1/2018 0:00 | 3 | 36,480 | 9/1/2018 0:00 |
10/1/2018 0:00 | 3 | 9,120 | 10/6/2018 0:00 |
10/1/2018 0:00 | 3 | 1,920 | 11/13/2019 0:00 |
10/1/2018 0:00 | 3 | 48,800 | 12/20/2018 0:00 |
@v-danhe-msft
Please find below the structure of current table:
Sdate | Sdate Year | Sdate Month | Employee ID | Actual Salary | salary proposed | Salary Difference | |||
1/1/2019 0:00 | 2019 | 1 | 1 | 10000 | 6,480 | 3,520 | |||
2/2/2019 0:00 | 2019 | 2 | 1 | 15000 | |||||
3/13/2019 0:00 | 2019 | 3 | 1 | 17000 | |||||
4/18/2018 0:00 | 2018 | 4 | 1 | 18000 | We are comparing the actual salary date to previous three months only | ||||
3/25/2018 0:00 | 2018 | 3 | 1 | 19000 | |||||
5/1/2018 0:00 | 2018 | 5 | 1 | 15000 | |||||
6/8/2018 0:00 | 2018 | 6 | 1 | 16000 | |||||
7/15/2018 0:00 | 2018 | 7 | 1 | 17500 | |||||
8/22/2018 0:00 | 2018 | 8 | 1 | 15600 | |||||
9/1/2018 0:00 | 2018 | 9 | 1 | 14700 | |||||
10/6/2018 0:00 | 2018 | 10 | 1 | 18000 | |||||
11/13/2019 0:00 | 2018 | 11 | 1 | 19000 | 42,690 | (23,690) | |||
12/20/2018 0:00 | 2018 | 12 | 1 | 21900 | 59,170 | (37,270) | |||
1/1/2019 0:00 | 2019 | 1 | 2 | 17500 | 41,000 | (23,500) | |||
2/2/2019 0:00 | 2019 | 2 | 2 | 15600 | |||||
3/13/2019 0:00 | 2019 | 3 | 2 | 14700 | |||||
4/18/2018 0:00 | 2018 | 4 | 2 | 18000 | |||||
3/25/2018 0:00 | 2018 | 3 | 2 | 19000 | |||||
5/1/2018 0:00 | 2018 | 5 | 2 | 15000 | |||||
6/8/2018 0:00 | 2018 | 6 | 2 | 16000 | |||||
7/15/2018 0:00 | 2018 | 7 | 2 | 17500 | |||||
8/22/2018 0:00 | 2018 | 8 | 2 | 17000 | |||||
9/1/2018 0:00 | 2018 | 9 | 2 | 18000 | |||||
10/6/2018 0:00 | 2018 | 10 | 2 | 19000 | |||||
11/13/2019 0:00 | 2018 | 11 | 2 | 15000 | 36,480 | (21,480) | |||
12/20/2018 0:00 | 2018 | 12 | 2 | 21344 | 48,000 | (26,656) | |||
1/1/2019 0:00 | 2019 | 1 | 3 | 19000 | 48,800 | (29,800) | |||
2/2/2019 0:00 | 2019 | 2 | 3 | 15000 | |||||
3/13/2019 0:00 | 2019 | 3 | 3 | 16000 | |||||
4/18/2018 0:00 | 2018 | 4 | 3 | 17500 | |||||
3/25/2018 0:00 | 2018 | 3 | 3 | 15600 | |||||
5/1/2018 0:00 | 2018 | 5 | 3 | 14700 | |||||
6/8/2018 0:00 | 2018 | 6 | 3 | 18000 | |||||
7/15/2018 0:00 | 2018 | 7 | 3 | 19000 | |||||
8/22/2018 0:00 | 2018 | 8 | 3 | 21900 | |||||
9/1/2018 0:00 | 2018 | 9 | 3 | 17500 | |||||
10/6/2018 0:00 | 2018 | 10 | 3 | 15600 | |||||
11/13/2019 0:00 | 2018 | 11 | 3 | 14700 | 59,170 | (44,470) | |||
12/20/2018 0:00 | 2018 | 12 | 3 | 18000 | 57,170 | (39,170) |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |