Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]) ) )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |