Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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   

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

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

5 Replies

  • Dan80's avatar
    Dan80
    Helper II
    9 years ago
    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's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
    binner
    Regular Visitor
    8 years ago

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

     

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