Forum Discussion
Report filter automatic result
Hello experts!
I need to filter the report to recognize the most recent date, then filter to only show results for the group of that date. For example...
The most recent event date on the database = 1/29/16 . So I need the filter to ONLY show results for 6 days in event# 152. This will prevent me from having to manually code the event# in the report each time a new event happens. Any ideas?
Im a newbie to this, so a simple-mans answer would be much appreciated :-D
| DATE | Event# | Day of Event | RevCat1 | RevCat2 | RevCat3 | RevCat4 | RevCat5 |
| 1/3/2016 | 150 | 1 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 |
| 1/3/2016 | 150 | 2 | $0.00 | $0.00 | $0.00 | $0.21 | ($104.00) |
| 1/4/2016 | 150 | 3 | $50.00 | $0.00 | $0.00 | $1,948.89 | $308.50 |
| 1/5/2016 | 151 | 1 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 |
| 1/6/2016 | 151 | 2 | $0.00 | $0.00 | $0.00 | $779.61 | $307.00 |
| 1/7/2016 | 151 | 3 | $0.00 | $0.00 | $413.00 | $897.78 | $1,011.00 |
| 1/8/2016 | 151 | 4 | $0.00 | $0.00 | $0.00 | $2,175.40 | ($618.00) |
| 1/9/2016 | 151 | 5 | $0.00 | $1,040.50 | $0.00 | ($21.06) | $614.50 |
| 1/10/2016 | 151 | 6 | $28.50 | $0.00 | $0.00 | $445.66 | $526.00 |
| 1/11/2016 | 152 | 1 | $0.00 | $0.00 | $0.00 | $810.06 | $662.50 |
| 1/12/2016 | 152 | 2 | $360.50 | $0.00 | $0.00 | $344.23 | $1,268.00 |
| 1/27/2016 | 152 | 3 | $0.00 | $0.00 | $0.00 | $720.20 | $1,301.00 |
| 1/27/2016 | 152 | 4 | $0.00 | $0.00 | $0.00 | $956.91 | $643.50 |
| 1/28/2016 | 152 | 5 | $0.00 | $0.00 | $287.00 | $1,158.48 | $2,290.50 |
| 1/29/2016 | 152 | 6 | $198.00 | $21,876.00 | $276.00 | $1,335.29 | $726.00 |
9 Replies
Anonymous Create the 2 Measures and then replace [Date] in your Table Visual with Last Date and [Event#] with Last Event#
Last Date = CALCULATE ( LASTDATE ( 'Table'[DATE] ), ALL ( 'Table' ) ) Last Event# = CALCULATE ( VALUES ( 'Table'[Event#] ), LASTDATE ( 'Table'[DATE] ), ALL ( 'Table' ) )Last Date formula will work as a column too...
Hi Pchapple,
According to your description, you need to only show most recent event date with add any filter, right?
I have tested it on my local environment, there is no any Minimum and Maximum option for a date value.
So you need to create another table to get only the lastest date for each event, and then use the new created table to create visuals.
Create a column in the original table
LatestDate = CALCULATE(MAX(Latest[DATE]),ALLEXCEPT(Latest,Latest[Event#]))
Then create a new table
LatestEvent = SUMMARIZE(Latest,Latest[Event#])
Add those columns to the new created table.
LatestDate = LOOKUPVALUE(Latest[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat1 = LOOKUPVALUE(Latest[RevCat1],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat2 = LOOKUPVALUE(Latest[RevCat2],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat3 = LOOKUPVALUE(Latest[RevCat3],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat4 = LOOKUPVALUE(Latest[RevCat4],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])
RevCat5 = LOOKUPVALUE(Latest[RevCat5],Latest[DATE],LatestEvent[LatestDate],Latest[Event#],LatestEvent[Event#])Regards,
Charlie Liao
Thanks v-caliao-msft
When you are referecning the table Latest... for example...
LatestDate = CALCULATE(MAX(Latest[DATE]),ALLEXCEPT(Latest,Latest[Event#]))
What table are you refering to? Is it the original table? When I try to create the new column in the original table as you described, its giving me an error.
Sorry for the newbie questions...
Paul
Hi Pchapple,
Yes, create calculated column in the original table. Please share us the error message, so that we can make further analysis.
Regards,
Charlie Liao
