cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpancratz Frequent Visitor
Frequent Visitor

How do I visualize only the latest date?

I have a table that will be updated weekly with the latest numbers (told sold, revenue, commission). It's updated weekly based on weeks out from the deadline.

I would love to create guages, cards, or other visualizations that only show the latest data. (In the sample below, 2/19/2017 or 7 weeks out)

Because each week's update is a total of everything YTD, total sums cannot be used for a guage/card. Rather than changing the value(s) every week, I'm trying to create a custom column, measure, or filter, that always shows me the most recent totals, based on date.

 

Weeks Out DateWeeks Out Total Sold  RevenueCommission
1/1/2017 0:0014                        2 $       200.00 $                50.00
1/8/2017 0:0013                        8 $       800.00 $             400.00
1/15/2017 0:0012                      14 $    1,400.00 $             750.00
1/22/2017 0:0011                      20 $    2,000.00 $          1,100.00
1/29/2017 0:0010                      26 $    2,600.00 $          1,450.00
2/5/2017 0:009                      32 $    3,200.00 $          1,800.00
2/12/2017 0:008                      38 $    3,800.00 $          2,150.00
2/19/2017 0:007                      44 $    4,400.00 $          2,500.00
2/26/2017 0:006   
3/5/2017 0:005   
3/12/2017 0:004   
3/19/2017 0:003   
3/26/2017 0:002   
4/2/2017 0:001   
4/9/2017 0:000   

 

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: How do I visualize only the latest date?

if your table is called [MyTable] you could create this as a table for your gauges

 

Hit the "New Table" button on the Modeling tab of the ribbon

 

Filter Table = FILTER(
                CROSSJOIN(
                    'MyTable',
                    SUMMARIZE(
                            FILTER('MyTable','MyTable'[Total Sold] >0),
                            "Latest week",
                            max('MyTable'[Weeks Out Date])
                            )
                        ),
                        [Latest week]='MyTable'[Weeks Out Date])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Dan80 Regular Visitor
Regular Visitor

Re: How do I visualize only the latest date?

Import data using power query. Filter out null values in total sales column. Add custom column to find minimum value of weeks out column List.Min(#"your previous step" ["weeks out"]). Add another custom column using if weeks out=min weeks out then "keep" else "delete". Filter out the deletes and boom. Latest data that will automatically change with new data
Phil_Seamark Super Contributor
Super Contributor

Re: How do I visualize only the latest date?

if your table is called [MyTable] you could create this as a table for your gauges

 

Hit the "New Table" button on the Modeling tab of the ribbon

 

Filter Table = FILTER(
                CROSSJOIN(
                    'MyTable',
                    SUMMARIZE(
                            FILTER('MyTable','MyTable'[Total Sold] >0),
                            "Latest week",
                            max('MyTable'[Weeks Out Date])
                            )
                        ),
                        [Latest week]='MyTable'[Weeks Out Date])

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

dpancratz Frequent Visitor
Frequent Visitor

Re: How do I visualize only the latest date?

Perfect. This seems to have done exactly what I was hoping. Thank you!

This is my first venture into the "New Table" (or even the "new column") modeling. Do you recommend any tutorials on use cases or how to use? 

binner Frequent Visitor
Frequent Visitor

Re: How do I visualize only the latest date?

For simplicity's sake you can create a measure using the LOOKUPVALUE function:

 

Measure = LOOKUPVALUE(Table[Lookup field],Table[Date],MAX(Table[Date]))

sewaktamang Regular Visitor
Regular Visitor

Re: How do I visualize only the latest date?

Hi,

You can just use IF ELSE and MAX DAX function to get latest date data.

You can take refrence from following post also. 

How to get latest date and display latest date data

 

Let me know if you face any issue 

 

Thanks

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,973 guests
Please welcome our newest community members: