Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpancratz
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
Phil_Seamark
Employee
Employee

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
sewaktamang
Frequent Visitor

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
Regular Visitor

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
Employee
Employee

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!

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
Helper II
Helper II

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.