Get last Number from the last Date Per Location

[petermb72]

I know that more than likely has been asked before, I am terrable at seaching for what I am looking for.  I have three locations, I want the last date data was entered for each of the locations.  Here is what the raw data looks like:

Date	Location	Number
1/1/2020	Fargo	10
1/2/2020	Fargo	15
1/1/2020	West Fargo	100
1/2/2020	West Fargo	105
1/3/2020	West Fargo	106
1/1/2020	Moorhead	1000
1/3/2020	Moorhead	1001

What I would like to see is:

Date	Location	Number
1/2/2020	Fargo	15
1/3/2020	West Fargo	106
1/3/2020	Moorhead	1001

When I am graphing this data and I did not get a submission from one of the locations, I would just like to have the graph just use the last available data point rather than just going to zero or causing an error.  I have been using a filter on the graph by only including data in the last 24 hours but it leaves a blank when when no data has been entered in the last 24 hours.  So there is probably many ways to fix this, I am up for any of them, I just want to not have my graphs show blank when data has not been entered.

Thanks,

Peter

[edhans]

Hello @petermb72

You'd need two measures. Here's what I got:

The last date is:

Last Date = 
CALCULATE(
    MAX('Table'[Date]),
    ALLEXCEPT('Table', 'Table'[Location])
)

y Last Amount es:

Last Amount = 
MAXX(
    FILTER(
        'Table',
        'Table'[Date] = [Last Date]
    ),
    'Table'[Number]
)

You would probably want to turn off totals in the table visual, as the totals I've shown are probably not significant, but maybe they are if you want an overall total regardless of location.

[petermb72]

YES! that did the trick doing it in two measures.  Thank you for your help and quick reply.  

[edhans]

Glad to be of assistance @petermb72 

Hope the rest of your project goes well.

[petermb72]

A bit of a snag.  When I do a card and try to get a grand total, I am getting a total that incorrect.  It is not the grand total.  What can  I do to get that to work correctly?

[edhans]

I'll have to redo it. I deleted my file aready.

But what is the right total, and how logically would I arrive at that? 

[petermb72]

I would expect the total to be 15+106+1001 for a total of 1122

[petermb72]

The number I am getting in the card is 1001 (the total from Moorhead for the 3rd.).  It is missing West Fargo on the 3rd as well as Fargo from the 2nd. strange

[edhans · Accepted solution]

Replace my Last Amount measure with this:

CALCULATE(
    SUM('Table'[Number]),
    VALUES('Table'[Location]),
    FILTER(
        'Table',
        'Table'[Date] = [Last Date]
    )
)

[petermb72]

PERFECT!

[petermb72]

Sorry about that, It looked like it was working perfectly until I tried to get a grand total on a card.