Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to find a sum by filtering by earliest date on each row. I have multiple records with the same ID number. Amount lost changes over time and my data keeps record of these changes with.
Here is an example of how my data is set up:
ID | Amount lost | As of Date |
1 | 100 | 1/1/2010 |
2 | 200 | 1/1/2010 |
3 | 300 | 1/1/2010 |
4 | 400 | 1/1/2010 |
1 | 150 | 1/1/2011 |
2 | 250 | 1/1/2011 |
3 | 350 | 1/1/2011 |
4 | 200 | 1/1/2011 |
5 | 500 | 1/1/2011 |
6 | 600 | 1/1/2011 |
1 | 150 | 1/1/2012 |
2 | 300 | 1/1/2012 |
3 | 400 | 1/1/2012 |
4 | 250 | 1/1/2012 |
5 | 450 | 1/1/2012 |
6 | 800 | 1/1/2012 |
7 | 700 | 1/1/2012 |
What I am trying to execute is taking the sum of the Amount Lost by Earliest As of Date of each ID. For this data it would be summing the Bolded records. My closest attempt is this:
Earliest Lost = CALCULATE(sum(LD[Amount Lost]), FILTER(LD, LD[As of Date]=MINX(values(LD[As of Date]), LD[As of Date]))).
This works for each individual record, pulling the earliest Amount Lost, but the overall sum only adds the 1/1/2010 as of dates and excludes all others.
Thanks in advance for the help
Solved! Go to Solution.
Hope this works and helps!
Measure = CALCULATE ( SUM ( LD[Amount lost] ), FILTER ( LD, LD[As of Date] = CALCULATE ( MIN ( LD[As of Date] ), ALLEXCEPT ( LD, LD[ID] ) ) ) )
Good Luck!
Hope this works and helps!
Measure = CALCULATE ( SUM ( LD[Amount lost] ), FILTER ( LD, LD[As of Date] = CALCULATE ( MIN ( LD[As of Date] ), ALLEXCEPT ( LD, LD[ID] ) ) ) )
Good Luck!
You sir are a genius.
Thanks
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |