How do I visualize only the latest date?

[Anonymous]

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 Date	Weeks Out	Total Sold	Revenue	Commission
1/1/2017 0:00	14	2	$       200.00	$                50.00
1/8/2017 0:00	13	8	$       800.00	$             400.00
1/15/2017 0:00	12	14	$    1,400.00	$             750.00
1/22/2017 0:00	11	20	$    2,000.00	$          1,100.00
1/29/2017 0:00	10	26	$    2,600.00	$          1,450.00
2/5/2017 0:00	9	32	$    3,200.00	$          1,800.00
2/12/2017 0:00	8	38	$    3,800.00	$          2,150.00
2/19/2017 0:00	7	44	$    4,400.00	$          2,500.00
2/26/2017 0:00	6
3/5/2017 0:00	5
3/12/2017 0:00	4
3/19/2017 0:00	3
3/26/2017 0:00	2
4/2/2017 0:00	1
4/9/2017 0:00	0

[sewaktamang]

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

[binner]

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

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

[Phil_Seamark · Accepted solution]

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])

[Anonymous]

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? 

[Dan80]

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