Using MAX to get most recent data

[SandeA]

Hi all, its me again! I'm trying to pull the most current data from a table to use in a gauge visual. The table contains data like this (this is a very small portion of the table):

The table contains multiple filenames so I'm trying to create a measure that will pull the most current size for a specific file name. I've got this part of the measure created:

FilenameDB6Size = CALCULATE (SELECTEDVALUE ( Database6[Size6] ), FILTER (Database6, Database6[Date6] = MAX (Database6[Date6] ) ))

but the value returned is "blank". I'm not sure if that is because I don't have it narrowed down to one specific file name or if there is a problem with the measure. 

I'd like to add a filter in there to only produce a value if the filename =" filename" (in this table example, that would be "ReportServer"). 

Thank you in advance for your help!

[SandeA]

Ok, I'll try to explain a little better. I have tables for 2 servers that contain database names and the size of that database on a given day. These tables are updated every day with that day's size of the database. This is a small sample of what is in the table as of yesterday (sorted desc by date):

Using the first database (AcctsPay) as a further example, when sorted by the database name, this is what shows:

As you can see, when the data was first tracked, the size was larger. On some of the databases, that number has gone up and down and back up again throughout the last year. 

I am trying to create either a card or gauge visual for each of the databases and a chart/graph for all of them so we can quickly see which are getting too high. Most of the measures I'm playing with are doing a SUM of the size per database. I'm trying to only pull whatever the latest value is. In the top table that would be the value for 7/21/2022, tomorrow it would be the value for 7/22/2022, etc. 

Does that make more sense? I'm not sure how to use google / one drive to share files, sorry

[Anonymous · Accepted solution]

Hi  @SandeA ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of measure [FilenameDB6Size] as below:

FilenameDB6Size = 
VAR _selfilename =
    SELECTEDVALUE ( Database6[FileName6] )
VAR _maxdate6 =
    CALCULATE (
        MAX ( Database6[Date6] ),
        FILTER ( ALLSELECTED( Database6) , Database6[FileName6] = _selfilename )
    )
RETURN
    CALCULATE (
        MAX ( Database6[Size6] ),
        FILTER ( Database6, Database6[Date6] = _maxdate6 )
    )

If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[SandeA]

@Anonymous I'm hoping you can help me once again... What you provided has worked great! However, there is a 4th column (FileName) in the table that I hadn't been using because I was told it wasn't needed. Well, I found out that some of the databases (Database6) have multiple files(FileName6) and the results were only pulling the largest sized file for the database, causing the total size to be wrong. I think I just need a GROUPBY function added. This is the visual before making any changes. The highlighted database is the one that has 3 filenames within it and each has its own value.

the GIS_Raster should be a total value of 125,209 by adding these 3 filename sizes together:

So I tried changing the code to this with the GROUPBY added:

DB6Size = VAR DBSize6 =

     SELECTEDVALUE ( Server6[FileName6])

VAR _maxdate6 =

     CALCULATE (

          MAX ( Server6[Date6] ),

          FILTER ( ALLSELECTED( Server6) , Server6[FileName6] = DBSize6 ),

          GROUPBY(Server6,Server6[Database6])

)

RETURN

     CALCULATE (

          MAX ( Server6[Size6] ),

          FILTER ( Server6, Server6[Date6] = _maxdate6 )

)

The results ended up removing GIS_Raster completely from the Top 20: 

Any help you can provide would be awesome! Thank you!

[parry2k]

@SandeA maybe your requirement is not clear or I'm missing it. Better to send a sample pbix file with the expected output. Share it using one/google drive, and remove sensitive information before sharing.

[parry2k]

@SandeA try this measure, change the column and table name as per your model.

Top Size = 
SUMX ( 
    Files, 
    VAR __file = Files[FileName]
    RETURN
    CALCULATE (
        SUM ( Files[Size] ),
        KEEPFILTERS(
            TOPN (
                1,
                FILTER ( ALL ( Files ), Files[FileName] =  __file ),
                Files[Date],
                DESC
            )
        )
    )
)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[SandeA]

nope, still not correct, sorry. It now is showing a value of 577k.  I don't think I want to use the TOPN because the current value isn't always the largest value. The sizes fluxuate. That is why I'm trying to get just the value for the most current day in the table. There is a job that updates the this table daily. 

[SandeA]

@claymcooper that's strange, I got the same results with yours as I did with the other one. Its still showing 551K instead of 198K 

[claymcooper]

@SandeA what happens if you put a filter on the page and select only that file name? Just curious

[parry2k]

@claymcooper this will give wrong information in the row context of Date6 column. FYI

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[claymcooper]

Change it to 
FilenameDB6Size = CALCULATE (SUM(SIZE6), Database6[Date6] = MAX (Database6[Date6] )

Additionally you could filter the data in Power Query to pull in only the most recent date if you didn't need the historical data.

[SandeA]

on another note, (pardon my ignorance here), I hear alot about Power Query, but have no idea how to access it. I've looked all over my desktop version of Power BI and can't see anything other than the Filters pane

[claymcooper]

Click the Transform Data button in the top ribbon. That opens Power Query

[parry2k]

@SandeA try this measure:

Most Size = CALCULATE ( SUM ( YourTable[SizeColumn] ), TOPN ( 1, ALL ( YourTable), YourTable[DateColumns], DESC ) )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[SandeA]

@parry2k that did pull data, but not the correct amount.  Based on the table, the Ferns filename should have a value of 198,327

but the value showing in a card from the measure is showing: