How to return data from the lastest date on a card viz

[Dee]

Hi all,

I have several columns, I would love to find the value based on the latest date with data

sample data

A                B                 C            

1.1.2019    XXX            60

2.2.2019    YYY            50

3.2.2019       0              0

4.3.2019        0             0

I expect to see the value on C, which is 50 as it is from the latest date with data.

Is there a way to ensure the data only returns for the data from the latest date, any help would be much appreciated.

ps. This is for a single card viz

[sturlaws · Accepted solution]

Hi @Dee 

try this measure:

Measure =
VAR _lastDate =
    CALCULATE (
        MAX ( 'Table'[A] );
        FILTER ( ALL ( 'Table' ); NOT ( ISBLANK ( 'Table'[C] ) ) && 'Table'[C] > 0 )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[C] ); FILTER ( 'Table'; 'Table'[A] = _lastDate ) )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Dee]

Hi @sturlaws 

Thank you for that,

The measure is however returning 0, which is my current problem and not 50 as expected.

[sturlaws]

It works in this demo report, according to your specifications and sample data. Are there some other requirements which you did not specify in your post?

[Dee]

Nop basically those are the only two columns of concern, the demo to the pbix seems o be a different one as it's reading end of day stock.

[sturlaws]

whoops, sorry, try this: demo