Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Below is a sample data that I am working with, I need to find the value in the same table based on Name and date going back 3 days.
Date | Name | Cost | Cost3daysago |
1/11/2019 | A | 10 | 0 |
1/11/2019 | B | 20 | 0 |
1/11/2019 | C | 30 | 0 |
1/11/2019 | D | 40 | 0 |
1/11/2019 | E | 50 | 0 |
1/12/2019 | A | 60 | 0 |
1/12/2019 | B | 70 | 0 |
1/12/2019 | C | 80 | 0 |
1/12/2019 | D | 90 | 0 |
1/12/2019 | E | 100 | 0 |
1/13/2019 | A | 110 | 0 |
1/13/2019 | B | 120 | 0 |
1/13/2019 | C | 130 | 0 |
1/13/2019 | D | 140 | 0 |
1/13/2019 | E | 150 | 0 |
1/14/2019 | A | 160 | 10 |
1/14/2019 | B | 170 | 20 |
1/14/2019 | C | 180 | 30 |
1/14/2019 | D | 190 | 40 |
1/14/2019 | E | 200 | 50 |
Please help
This is what I have so far which is giving me the result but it requires me to create a column specific for 3 days. Hope there is much effective way fo doing it so I have more flexibility with respect to how many days I can go back.
Solved! Go to Solution.
You could use a variable to get the date 3 days before
Cost3daysagoX = VAR _DateBefore = Table[Date] - 3 RETURN CALCULATE( SUM(Table[Cost]) + 0, FILTER( ALL(Table4), Table[Date] = _DateBefore && Table[Name] = EARLIER( Table[Name]) ) )
You could use a variable to get the date 3 days before
Cost3daysagoX = VAR _DateBefore = Table[Date] - 3 RETURN CALCULATE( SUM(Table[Cost]) + 0, FILTER( ALL(Table4), Table[Date] = _DateBefore && Table[Name] = EARLIER( Table[Name]) ) )
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 |
---|---|
105 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |